package com.gome.ocean.controller.websql;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.Page;
import com.gome.framework.AppContext;
import com.gome.framework.Env;
import com.gome.framework.util.SystemInfoUtil;
import com.gome.ocean.common.constants.Constants;
import com.gome.ocean.common.constants.WebSqlContants;
import com.gome.ocean.common.enums.DataBaseType;
import com.gome.ocean.common.enums.DataSource;
import com.gome.ocean.common.exception.CommonDataBaseException;
import com.gome.ocean.common.utils.CompressFileUtils;
import com.gome.ocean.common.utils.DBUtil;
import com.gome.ocean.common.utils.ResponsePage;
import com.gome.ocean.common.utils.SqlValidateHelper;
import com.gome.ocean.common.utils.StringEscapeUtil;
import com.gome.ocean.common.utils.TimeUtils;
import com.gome.ocean.dao.model.datasource.ExportBean;
import com.gome.ocean.dao.model.datasource.SQLUploadBean;
import com.gome.ocean.dao.model.datax.SqlChildrenNode;
import com.gome.ocean.dao.model.datax.SqlParentNode;
import com.gome.ocean.dao.model.datax.SqlTplMenuVO;
import com.gome.ocean.dao.model.dsm.CommonDataBaseDetail;
import com.gome.ocean.dao.model.websql.DBAllCharsetAndCollation;
import com.gome.ocean.dao.model.websql.DBEditTableBean;
import com.gome.ocean.dao.model.websql.DataBaseVO;
import com.gome.ocean.dao.model.websql.DataProcedureBean;
import com.gome.ocean.dao.model.websql.DisplayColumnsVO;
import com.gome.ocean.dao.model.websql.ExportDataSQL;
import com.gome.ocean.dao.model.websql.GUserFrequentlySql;
import com.gome.ocean.dao.model.websql.MetadataVO;
import com.gome.ocean.dao.model.websql.MysqlColumnJsonBean;
import com.gome.ocean.dao.model.websql.MysqlIndexJsonBean;
import com.gome.ocean.dao.model.websql.MysqlRefJsonBean;
import com.gome.ocean.dao.model.websql.OpenTableStruct;
import com.gome.ocean.dao.model.websql.ProcedureInnerBean;
import com.gome.ocean.dao.model.websql.SqlResultVO;
import com.gome.ocean.dao.model.websql.SqlTemplateVo;
import com.gome.ocean.dao.model.websql.TableVO;
import com.gome.ocean.dao.websql.WebSqlEngine;
import com.gome.ocean.service.base.AuthorityBaseController;
import com.gome.ocean.service.datax.IDataCharsetService;
import com.gome.ocean.service.datax.IExportService;
import com.gome.ocean.service.datax.ISQLUploadService;
import com.gome.ocean.service.datax.IWebSqlService;
import com.gome.ocean.service.websql.CommonDataSourceService;
import com.gome.ocean.service.websql.SqlHandleService;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;

/**
 * @author zhangguoqing 2016年9月18日 下午4:51:22
 * @since 5.0.0
 */
@Controller
@RequestMapping("/dataManage")
public class DataManageController extends AuthorityBaseController {
 
    @Autowired
    private CommonDataSourceService commonDataSourceService;
    @Autowired
    private IWebSqlService          iWebSqlService;
    @Autowired
    private WebSqlEngine            webSqlEngine;
    @Autowired
    private SqlHandleService        sqlHandleService;
    @Autowired
    private IDataCharsetService     iDataCharsetService;
    @Autowired
    private ISQLUploadService       uploadService;
    @Autowired
    private IExportService          iExportService;

    private static final Logger LOG = LoggerFactory.getLogger(DataManageController.class);

    // 获取结构数据
    private StringBuffer getStructBuffer(Object structObj, StringBuffer structBuff) {
        DBEditTableBean editStruct = (DBEditTableBean) structObj;
        structBuff.append(editStruct.getCreateScript());
        return structBuff;
    }

    // 获取insert数据
    private StringBuffer getMetaDataBuffer(Object result, String tables, StringBuffer insertBuff) {
        SqlResultVO resultVo = (SqlResultVO) result;
        insertBuff.append("INSERT INTO ").append(tables).append("(");
        List<MetadataVO> metaData = resultVo.getMetadata();
        StringBuffer metaBuff = new StringBuffer();
        for (MetadataVO meta : metaData) {
            metaBuff.append(meta.getRealName()).append(",");
        }
        insertBuff.append(metaBuff.deleteCharAt(metaBuff.length() - 1)).append(") VALUES ").append("\r\n");
        StringBuffer dataDetailBuff = new StringBuffer();
        List<Map<String, Object>> dataListMap = resultVo.getData();
        if (dataListMap != null && dataListMap.size() > 0) {
            for (Map<String, Object> dataMap : dataListMap) {
                dataDetailBuff.append("(");
                for (int i = 1; i <= metaData.size(); i++) {
                    if (dataMap.get("COLUMN_" + i) != null) {
                        String value = dataMap.get("COLUMN_" + i).toString();
                        if (StringUtils.isBlank(value)) {
                            dataDetailBuff.append("null").append(",");
                        } else {
                            dataDetailBuff.append("'").append(StringEscapeUtil.escapeJavaScript(value)).append("',");
                        }
                    } else {
                        dataDetailBuff.append("null").append(",");
                    }
                }
                dataDetailBuff.deleteCharAt(dataDetailBuff.length() - 1).append("),").append("\r\n");
            }
            insertBuff.append(dataDetailBuff.deleteCharAt(dataDetailBuff.lastIndexOf(",")).append(";"));
        }
        return insertBuff;
    }

    @RequestMapping("/insert")
    public ModelAndView insert(ModelAndView mav) {
        mav.setViewName("gmwebsql/sql_module/sql_parent_node");
        return mav;
    }

    @RequestMapping("/isExisted")
    @ResponseBody
    public boolean isExisted(String parentName, String type) {
        try {
            Map<String, Object> map = Maps.newHashMap();
            map.put("parentName", parentName);
            map.put("type", Integer.parseInt(type));
            List<SqlParentNode> list = iWebSqlService.getSqlParentNodeList(map);
            if (list != null && list.size() > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return false;
    }

    @SuppressWarnings("rawtypes")
    @RequestMapping("/insertParentNode")
    @ResponseBody
    public ResponsePage insertParentNode(String parentName, String type) {
        ResponsePage page = new ResponsePage();
        try {
            SqlParentNode node = new SqlParentNode();
            node.setParentName(parentName);
            node.setType(Integer.parseInt(type));
            iWebSqlService.insertSqlParentModule(node);
            return page.setSuccessMsg("成功");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return page.setErrorMsg("失败");
    }

    @RequestMapping("/ajaxFindParentNode")
    @ResponseBody
    public List<SqlParentNode> ajaxFindParentNode(String type) {
        List<SqlParentNode> sqlParentLists = new ArrayList<SqlParentNode>();
        try {
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("type", Integer.parseInt(type));
            sqlParentLists = iWebSqlService.getSqlParentNodeList(map);
            if (sqlParentLists != null && sqlParentLists.size() > 0) {
                return sqlParentLists;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sqlParentLists;
    }

    @RequestMapping("/insertChildrenNode")
    public ModelAndView insertChildrenNode(ModelAndView mav) {

        mav.setViewName("gmwebsql/sql_module/sql_children_node");
        return mav;
    }

    @SuppressWarnings("rawtypes")
    @RequestMapping("/ajaxInsertChildrenNode")
    @ResponseBody
    public ResponsePage insertAjaxChildrenNode(String sqlSubType, String sqlCategory, String sqlComment,
                                               String sqlChildrenNodeName) {
        ResponsePage page = new ResponsePage();
        try {
            SqlChildrenNode childrenNode = new SqlChildrenNode();
            childrenNode.setChildrenName(sqlChildrenNodeName);
            childrenNode.setComment(sqlComment);
            childrenNode.setParentId(Integer.parseInt(sqlSubType));
            childrenNode.setSqlCategory(Integer.parseInt(sqlCategory));
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("sqlCategory", Integer.parseInt(sqlCategory));
            map.put("childrenName", sqlChildrenNodeName);
            List<SqlChildrenNode> list = iWebSqlService.getSqlChildrenNodeList(map);
            if (list != null && list.size() > 0) {
                return page.setErrorMsg("节点已经存在");
            }
            iWebSqlService.insertSqlChildrenModule(childrenNode);
            return page.setSuccessMsg("插入成功");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return page.setErrorMsg("失败");
    }

    @RequestMapping("/find")
    public ModelAndView findNode(ModelAndView mav) {
        mav.setViewName("gmwebsql/sql_module/sql_node_find");
        return mav;
    }

    @RequestMapping("/queryTplContentById")
    @ResponseBody
    public Map<String, Object> queryTplContentById(String id) {
        Map<String, Object> map = Maps.newHashMap();
        SqlChildrenNode node = iWebSqlService.getSqlChildrenNode(Integer.parseInt(id));
        map.put("root", node);
        map.put("success", true);
        return map;
    }

    @RequestMapping("/queryTable")
    public ModelAndView openTable(HttpServletRequest request, ModelAndView mav, Long dataBaseId, Long dataSourceId,
                                  String baseType, Integer dataSource, String dbName,
                                  String objectName) throws SQLException {
        Env env = AppContext.getEnv();
        String envirVal = env.get("environValue");
        mav.getModel().put("smsLoginDiamondValue", envirVal);
        String isLocal = request.getParameter("isLocal");
        String email = super.getCurrentUser();
        try {
            CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
            if ("isLocal".equals(isLocal)) {
                mav.addObject("isLocal", isLocal);
                dataBaseDetail = commonDataSourceService.findDataBase(dataBaseId,
                    dataSourceId,
                    DataSource.getDataBaseType(dataSource),
                    DataBaseType.getDataBaseType(baseType));
            } else {
                dataBaseDetail = commonDataSourceService.getSingleDataBaseByHttp(dataSourceId,
                    DataBaseType.getDataBaseType(baseType.toLowerCase()),
                    email,
                    this.getDBAInterfaceIp(),
                    this.getDiffEnvironmentValue());
            }
            Set<OpenTableStruct> tableStruct = Sets.newLinkedHashSet();
            Set<OpenTableStruct> tableStructIndex = Sets.newLinkedHashSet();
            Set<OpenTableStruct> tableStructData = Sets.newLinkedHashSet();
            List<TableVO> columnLists = webSqlEngine.getTableColumnDetail(dataBaseDetail, dbName, objectName);
            List<TableVO> indexLists = webSqlEngine.getTableIndexDetail(dataBaseDetail, dbName, objectName);
            for (TableVO index : indexLists) {
                OpenTableStruct tableStr = new OpenTableStruct();
                String[] indexName = index.getRealText().split("（");
                tableStr.setPrikey(true);
                tableStr.setName(indexName[1].split("）")[0]);
                tableStructIndex.add(tableStr);
            }
            for (TableVO column : columnLists) {
                OpenTableStruct tableStr = new OpenTableStruct();
                for (OpenTableStruct tableIndex : tableStructIndex) {
                    if (!tableIndex.getName().equals(column.getRealName())) {
                        tableStr.setName(column.getRealName());
                        tableStructData.add(tableStr);
                    }
                }

            }
            tableStruct.addAll(tableStructIndex);
            tableStruct.addAll(tableStructData);
            mav.addObject("dataBaseDetail", dataBaseDetail);
            mav.addObject("dataBaseId", dataBaseId);
            mav.addObject("dataSourceId", dataSourceId);
            mav.addObject("baseType", baseType);
            mav.addObject("dataSource", dataSource);
            mav.addObject("dbName", dbName);
            mav.addObject("objectName", objectName);
            mav.addObject("tableStruct", JSON.toJSONString(tableStruct));
        } catch (CommonDataBaseException e) {
            LOG.error(e.toString());
            mav.setViewName("404");
        }
        mav.setViewName("gmwebsql/open_table");
        return mav;
    }

    @RequestMapping(value = "/createNewTablePage", method = RequestMethod.GET)
    @ResponseBody
    public ModelAndView createNewTablePage(ModelAndView mav, HttpServletRequest request, Long dataBaseId,
                                           Long dataSourceId, String baseType, Integer dataSource, String dbName,
                                           String objectName, String operation) throws SQLException {
        String email = super.getCurrentUser();
        String isLocal = request.getParameter("isLocal");
        try {
            CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
            if ("isLocal".equals(isLocal)) {
                mav.addObject("isLocal", isLocal);
                dataBaseDetail = commonDataSourceService.findDataBase(dataBaseId,
                    dataSourceId,
                    DataSource.getDataBaseType(dataSource),
                    DataBaseType.getDataBaseType(baseType));
            } else {
                dataBaseDetail = commonDataSourceService.getSingleDataBaseByHttp(dataSourceId,
                    DataBaseType.getDataBaseType(baseType.toLowerCase()),
                    email,
                    this.getDBAInterfaceIp(),
                    this.getDiffEnvironmentValue());
            }
            List<String> userContainsProject = this.getCurrentUserProjects();
            List<Map<String, Object>> proLists = Lists.newArrayList();
            if (userContainsProject != null && userContainsProject.size() > 0) {
                for (String uPro : userContainsProject) {
                    Map<String, Object> map = Maps.newHashMap();
                    map.put("data", uPro);
                    map.put("label", uPro);
                    proLists.add(map);
                }
            } else {
                Map<String, Object> map = Maps.newHashMap();
                map.put("data", "");
                map.put("label", "");
                proLists.add(map);
            }
            mav.addObject("proLists", JSON.toJSONString(proLists));
            List<DataBaseVO> allDBInfo = webSqlEngine.getDataBase(dataBaseDetail);
            mav.addObject("allDBInfo", JSON.toJSONString(allDBInfo));
            Map<String, Object> versionMaps = webSqlEngine.getDataBaseInfo(dataBaseDetail);
            mav.addObject("versionMaps", versionMaps);
            mav.addObject("dataBaseDetail", dataBaseDetail);
            mav.addObject("dataBaseId", dataBaseId);
            mav.addObject("dataSourceId", dataSourceId);
            mav.addObject("baseType", baseType);
            mav.addObject("dataSource", dataSource);
            mav.addObject("dbName", dbName);
            mav.addObject("objectName", StringUtils.isEmpty(objectName) ? "" : objectName);
            mav.addObject("isLocal", isLocal);
            mav.addObject("operation", operation);
        } catch (CommonDataBaseException e) {
            LOG.error(e.toString());
            mav.setViewName("404");
        }
        mav.setViewName("gmwebsql/newtable/insert_newtable_page");
        return mav;
    }

    // scope_application：3表示为当前库
    @SuppressWarnings("rawtypes")
    @RequestMapping(value = "/saveUserSqlHandle", method = RequestMethod.POST)
    @ResponseBody
    public ResponsePage saveUserSqlHandle(ResponsePage page, HttpServletRequest request,
                                          GUserFrequentlySql frequentlySql) {
        String tableName = request.getParameter("datatableName");
        if (tableName.contains("SQL")) {
            tableName = "SQL 语句";
        }
        frequentlySql.setDatatableName(tableName);
        frequentlySql.setAddTime(new Date());
        frequentlySql.setModifyTime(new Date());
        frequentlySql.setUserEmail(super.getCurrentUser());
        page.setSuccess(sqlHandleService.saveUserSql(frequentlySql));
        return page;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    @RequestMapping(value = "/showUserSql", method = RequestMethod.POST)
    @ResponseBody
    public ResponsePage showUserSqlPost(ResponsePage page, HttpServletRequest request) {

        Map<String, Object> param = new HashMap<String, Object>();
        param.put("datatableName", request.getParameter("datatableName"));
        param.put("databaseId",
            request.getParameter("databaseId") == null ? "" : Long.parseLong(request.getParameter("databaseId")));
        param.put("userEmail", super.getCurrentUser());
        List<GUserFrequentlySql> frequentlySqls = sqlHandleService.getUserFrequentlySqlByParam(param);
        if (!(frequentlySqls == null) && frequentlySqls.size() > 0) {
            page.setJustList(frequentlySqls, "成功查询数据！");
            page.setSuccess(true);
        } else {
            page.setErrorMsg("没有数据");
            page.setSuccess(false);
        }
        return page;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    @RequestMapping(value = "/showUserSql", method = RequestMethod.GET)
    @ResponseBody
    public ResponsePage showUserSqlGet(ResponsePage page, HttpServletRequest request) {

        Map<String, Object> param = new HashMap<String, Object>();
        param.put("datatableName", request.getParameter("datatableName"));
        param.put("databaseId",
            request.getParameter("databaseId") == null ? "" : Long.parseLong(request.getParameter("databaseId")));
        param.put("userEmail", super.getCurrentUser());
        List<GUserFrequentlySql> frequentlySqls = sqlHandleService.getUserFrequentlySqlByParam(param);
        if (!(frequentlySqls == null) && frequentlySqls.size() > 0) {
            page.setJustList(frequentlySqls, "成功查询数据！");
            page.setSuccess(true);
        } else {
            page.setErrorMsg("没有数据");
            page.setSuccess(false);
        }
        return page;
    }

    @SuppressWarnings("rawtypes")
    @RequestMapping(value = "/removeUserSql", method = RequestMethod.POST)
    @ResponseBody
    public ResponsePage removeUserSql(ResponsePage page, HttpServletRequest request) {
        Long id = Long.valueOf(request.getParameter("id"));
        page.setSuccess(sqlHandleService.removeUserSql(id));
        return page;
    }

    @SuppressWarnings("rawtypes")
    @RequestMapping(value = "/updateUserSql", method = RequestMethod.POST)
    @ResponseBody
    public ResponsePage updateUserSql(ResponsePage page, HttpServletRequest request, GUserFrequentlySql frequentlySql) {
        page.setSuccess(sqlHandleService.updateByPrimaryKeySelective(frequentlySql));
        return page;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    @RequestMapping(value = "/getUserSqlInfoById", method = RequestMethod.POST)
    @ResponseBody
    public ResponsePage getUserSqlInfoById(ResponsePage page, HttpServletRequest request) {
        page.setSuccess(false);
        Long id = Long.valueOf(request.getParameter("id"));
        GUserFrequentlySql frequentlySql = sqlHandleService.getGUserFrequentlySqlById(id);
        if (!(frequentlySql == null)) {
            page.setSingleObject(frequentlySql, "查询成功");
            page.setSuccess(true);
        }
        return page;
    }

    // sql模板
    @RequestMapping(value = "/insertSqlTempPage", method = RequestMethod.GET)
    public ModelAndView insertSqlTempPage(ModelAndView mav, HttpServletRequest request) {
        mav.setViewName("/gmwebsql/sql_module/sql_temp_page");
        return mav;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    @RequestMapping(value = "/getSqlTempByParentId", method = RequestMethod.POST)
    @ResponseBody
    public ResponsePage getSqlTempByParentId(ResponsePage page, HttpServletRequest request) {
        page.setSuccess(false);
        Map<String, Object> param = Maps.newHashMap();
        param.put("parent_id", request.getParameter("pid"));
        param.put("sql_category", request.getParameter("sql_category"));
        List<SqlTplMenuVO> sqlTplMenuVOs = iWebSqlService.getSqlTplMenuVO(param);
        if (!(sqlTplMenuVOs == null) && !(sqlTplMenuVOs.size() == 0)) {
            page.setJustList(sqlTplMenuVOs, "查询成功");
            page.setSuccess(true);
        }

        return page;
    }

    @SuppressWarnings("rawtypes")
    @RequestMapping(value = "/insertSqlTempMenu", method = RequestMethod.POST)
    @ResponseBody
    public ResponsePage insertSqlTempMenu(ResponsePage page, HttpServletRequest request, SqlTplMenuVO sqlTplMenuVO) {
        page.setSuccess(iWebSqlService.insertSelective(sqlTplMenuVO));
        return page;
    }

    @SuppressWarnings("rawtypes")
    @RequestMapping(value = "/updateSqlTempMenu", method = RequestMethod.POST)
    @ResponseBody
    public ResponsePage updateSqlTempMenu(ResponsePage page, HttpServletRequest request, SqlTplMenuVO sqlTplMenuVO) {
        page.setSuccess(iWebSqlService.updateByPrimaryKeySelective(sqlTplMenuVO));
        return page;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    @RequestMapping(value = "/getSqlTempList", method = RequestMethod.POST)
    @ResponseBody
    public ResponsePage getSqlTempList(ResponsePage page, HttpServletRequest request) {
        page.setSuccess(false);
        Map<String, Object> param = Maps.newHashMap();
        param.put("parent_id", request.getParameter("pid"));
        param.put("sql_category", request.getParameter("sql_category"));

        List<SqlTplMenuVO> sqlTplMenuVOs = iWebSqlService.getSqlTplMenuVOCollection(param);
        if (!(sqlTplMenuVOs == null) && !(sqlTplMenuVOs.size() == 0)) {
            page.setJustList(sqlTplMenuVOs, "查询成功");
            page.setSuccess(true);
        }

        return page;
    }

    @SuppressWarnings("rawtypes")
    @RequestMapping(value = "/removeSqlTpl", method = RequestMethod.POST)
    @ResponseBody
    public ResponsePage removeSqlTpl(ResponsePage page, HttpServletRequest request, Long id) {
        page.setSuccess(iWebSqlService.deleteByPrimaryKey(id));
        return page;
    }

    @RequestMapping(value = "/findCollateByCharset", method = RequestMethod.GET)
    @ResponseBody
    public Map<String, Object> findCollateByCharset(HttpServletRequest request) {
        try {
            String ruleType = request.getParameter("charset");
            String dbtype = request.getParameter("dbtype");
            Map<String, Object> map = iDataCharsetService.getDataCharsetMapByRuleTypeAndDBType(ruleType,
                dbtype,
                WebSqlContants.CHECKOUT_RULE);
            if (map != null && map.size() > 0) {
                return map;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @RequestMapping(value = "/showTableColumns", method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> showTableColumns(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            String email = super.getCurrentUser();
            String isLocal = request.getParameter("isLocal");
            String baseType = request.getParameter("baseType");
            String dataBaseId = request.getParameter("dataBaseId");
            String dataSourceId = request.getParameter("dataSourceId");
            String dataSource = request.getParameter("dataSource");
            CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
            if ("isLocal".equals(isLocal)) {
                dataBaseDetail = commonDataSourceService.findDataBase(Long.parseLong(dataBaseId),
                    Long.parseLong(dataSourceId),
                    DataSource.getDataBaseType(Integer.parseInt(dataSource)),
                    DataBaseType.getDataBaseType(baseType));
            } else {
                dataBaseDetail = commonDataSourceService.getSingleDataBaseByHttp(Long.parseLong(dataSourceId),
                    DataBaseType.getDataBaseType(baseType.toLowerCase()),
                    email,
                    this.getDBAInterfaceIp(),
                    this.getDiffEnvironmentValue());
            }
            if (dataBaseDetail == null) {
                map.put("root", "数据源获取失败!");
                map.put("failure", true);
                map.put("success", false);
                return map;
            }

            String dbName = request.getParameter("dbName");
            String tableName = request.getParameter("tableName");
            map = webSqlEngine.showTableAllColumns(dataBaseDetail, dbName, tableName);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }

    @SuppressWarnings("unused")
    @RequestMapping(value = "/showAddTableScript", method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> showAddTableScript(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        List<String> showSqlStr = Lists.newArrayList();
        // 拼接SQL
        StringBuilder sb = new StringBuilder();
        String tableName = request.getParameter("tableName");
        String comment = request.getParameter("comment");
        String engine = request.getParameter("engine");
        String charset = request.getParameter("charset");
        String collate = request.getParameter("collate");
        String autoIncrement = request.getParameter("autoIncrement");
        String rowFormat = request.getParameter("rowFormat");
        String avgRowLength = request.getParameter("avgRowLength");
        String minRows = request.getParameter("minRows");
        String maxRows = request.getParameter("maxRows");
        String keyBlockSize = request.getParameter("keyBlockSize");
        String dataDirectory = request.getParameter("dataDirectory");
        String indexDirectory = request.getParameter("indexDirectory");
        String union = request.getParameter("union");
        String insertMethod = request.getParameter("insertMethod");
        String packKeys = request.getParameter("packKeys");
        String columns = request.getParameter("columns");
        String indexes = request.getParameter("indexes");
        String partitionContent = request.getParameter("partitionContent");
        String references = request.getParameter("references");
        String dbName = request.getParameter("dbName");
        String dbType = request.getParameter("dbType");
        // Oracle
        if (dbType.equals(DataBaseType.Oracle.getTypeFullName())) {
            try {
                StringBuilder fieldSb = new StringBuilder();
                if (StringUtils.isNotBlank(columns)) {
                    sb.append("CREATE TABLE ").append(dbName).append(".").append(tableName).append("(");
                    List<MysqlColumnJsonBean> columnLists = JSONArray.parseArray(columns, MysqlColumnJsonBean.class);
                    for (MysqlColumnJsonBean column : columnLists) {
                        if (StringUtils.isNotBlank(column.getColumnName())) {
                            sb.append("\n").append(column.getColumnName()).append(" ");
                        }
                        if (StringUtils.isNotBlank(column.getColumnType())) {
                            sb.append(column.getColumnType());
                            if (StringUtils.isNotBlank(column.getLength())) {
                                sb.append("(").append(column.getLength()).append(")").append(" ");
                            }
                        }
                        if (StringUtils.isNotBlank(column.getDefaults())) {
                            sb.append(" DEFAULT ").append(column.getDefaults());
                        }
                        if (!column.getNullable()) {
                            sb.append(" NOT NULL ");
                        }
                        // 字段注释
                        if (StringUtils.isNotBlank(column.getComments())) {
                            fieldSb.append("COMMENT ON COLUMN ")
                                .append(dbName)
                                .append(".")
                                .append(tableName)
                                .append(".")
                                .append(column.getColumnName())
                                .append(" IS '")
                                .append(column.getComments())
                                .append("';");
                        }
                        sb.append(",");
                    }
                    StringBuffer priBuf = new StringBuffer();
                    priBuf.append(" PRIMARY KEY (");
                    for (MysqlColumnJsonBean column : columnLists) {
                        if (column.getPrimaryKey()) {
                            priBuf.append("`").append(column.getColumnName()).append("`").append(",");
                        }
                    }
                    priBuf.deleteCharAt(priBuf.length() - 1).append(")");
                    if (priBuf.indexOf("`") > 1) {
                        sb.append(priBuf.toString().replaceAll("`", "")).append(",");
                    }
                }
                // 外键
                if (StringUtils.isNotBlank(references)) {
                    StringBuffer refBf = new StringBuffer();
                    List<MysqlRefJsonBean> refLists = JSONArray.parseArray(references, MysqlRefJsonBean.class);
                    if (refLists != null && refLists.size() > 0) {
                        for (MysqlRefJsonBean ref : refLists) {
                            if (StringUtils.isNotBlank(ref.getName())) {
                                refBf.append(" CONSTRAINT ").append(ref.getName()).append(" FOREIGN KEY (");
                            }
                            if (StringUtils.isNotBlank(ref.getColumns())) {
                                StringBuffer includeColumn = new StringBuffer();
                                StringBuffer refColumn = new StringBuffer();
                                String[] includeColArr = ref.getColumns().split(",");
                                for (String i : includeColArr) {
                                    includeColumn.append(i).append(",");
                                }
                                if (StringUtils.isNotBlank(ref.getRefDBName())
                                    || StringUtils.isNotBlank(ref.getRefTableName())
                                    || StringUtils.isNotBlank(ref.getRefColumns())) {
                                    refBf.append(includeColumn.deleteCharAt(includeColumn.length() - 1))
                                        .append(") REFERENCES ")
                                        .append(ref.getRefDBName())
                                        .append(".")
                                        .append(ref.getRefTableName())
                                        .append(" (");
                                    String[] refColArr = ref.getRefColumns().split(",");
                                    for (String r : refColArr) {
                                        refColumn.append(r).append(",");
                                    }
                                    refBf.append(refColumn.deleteCharAt(refColumn.length() - 1)).append(")");
                                }
                                refBf.append(",");
                            }
                        }
                        sb.append(refBf);
                    }
                }
                sb.deleteCharAt(sb.lastIndexOf(",")).append(");");
                // 字段注释
                if (fieldSb.length() > 1) {
                    sb.append(";").append("\n").append(fieldSb);
                }
                // 索引
                if (StringUtils.isNotEmpty(indexes)) {
                    StringBuilder indexSb = new StringBuilder();
                    List<MysqlIndexJsonBean> indexLists = JSONArray.parseArray(indexes, MysqlIndexJsonBean.class);
                    if (indexLists != null && indexLists.size() > 0) {
                        for (MysqlIndexJsonBean index : indexLists) {
                            StringBuffer commStr = new StringBuffer();
                            if (StringUtils.isNotBlank(index.getColumns())) {
                                String column = "";
                                if (index.getColumns().contains("(")) {
                                    column = index.getColumns().substring(0, index.getColumns().indexOf("("));
                                } else {
                                    column = index.getColumns();
                                }
                                commStr.append(index.getName())
                                    .append(" ON ")
                                    .append(dbName)
                                    .append(".")
                                    .append(tableName)
                                    .append("(")
                                    .append(column)
                                    .append(" ")
                                    .append(index.getMethod())
                                    .append(")");
                            }
                            if (StringUtils.isNotBlank(index.getType())) {
                                if (index.getType().equals("INDEX")) {
                                    indexSb.append("CREATE INDEX ").append(dbName).append(".").append(commStr);
                                } else if (index.getType().equals("UNIQUE")) {
                                    indexSb.append("CREATE UNIQUE INDEX ").append(dbName).append(".").append(commStr);
                                } else if (index.getType().equals("BITMAP")) {
                                    indexSb.append("CREATE BITMAP INDEX ").append(dbName).append(".").append(commStr);
                                }
                            }
                            if (StringUtils.isNotBlank(index.getColumns())) {
                                indexSb.append(";");
                            }
                        }
                        sb.append(indexSb);
                    }
                }
                showSqlStr.add(sb.deleteCharAt(sb.length() - 1).toString());
                map.put("root", showSqlStr);
                map.put("success", true);
                map.put("failure", false);
                map.put("dbName", dbName);
            } catch (Exception e) {
                map.put("root", e.getMessage());
                map.put("success", false);
                map.put("failure", true);
                map.put("dbName", dbName);
            }
        } else {
            // MYSQL
            // 列
            try {
                StringBuilder checkSbuiler = new StringBuilder();
                if (StringUtils.isNotEmpty(columns)) {
                    sb.append("CREATE TABLE `").append(tableName).append("` (");
                    List<MysqlColumnJsonBean> columnLists = JSONArray.parseArray(columns, MysqlColumnJsonBean.class);
                    for (MysqlColumnJsonBean column : columnLists) {
                        if (StringUtils.isNotEmpty(column.getColumnName())) {
                            sb.append("`").append(column.getColumnName()).append("`").append(" ");
                        }
                        if (StringUtils.isNotEmpty(column.getColumnType())) {
                            sb.append(column.getColumnType());
                            if (StringUtils.isNotEmpty(column.getLength())) {
                                sb.append("(").append(column.getLength()).append(")").append(" ");
                            }
                        }
                        if (!column.getNullable()) {
                            sb.append(" NOT NULL ");
                        } else {
                            sb.append(" NULL ");
                        }
                        if (column.getAutoIncrement()) {
                            sb.append(" AUTO_INCREMENT ");
                        }
                        if (StringUtils.isNotEmpty(column.getDefaults())) {
                            if (column.getColumnType().contains("int") || column.getColumnType().contains("float")
                                || column.getColumnType().contains("double")
                                || column.getColumnType().contains("decimal")) {
                                sb.append(" DEFAULT ").append(column.getDefaults());
                            } else {
                                sb.append(" DEFAULT '").append(column.getDefaults()).append("'");
                            }
                        }
                        if (column.getUnsigned()) {
                            sb.append(" UNSIGNED ");
                        }
                        if (column.getBinary()) {
                            sb.append(" BINARY ");
                        }
                        if (column.getZerofill()) {
                            sb.append(" ZEROFILL ");
                        }
                        if (StringUtils.isNotEmpty(column.getComments())) {
                            sb.append(" COMMENT '").append(column.getComments()).append("' ");
                        }
                        sb.append(",").append("\n");
                    }
                    StringBuilder prikeySb = new StringBuilder();
                    prikeySb.append("PRIMARY KEY (");
                    for (MysqlColumnJsonBean column : columnLists) {
                        if (column.getPrimaryKey()) {
                            prikeySb.append("`").append(column.getColumnName()).append("`").append(",");
                        }
                    }
                    prikeySb.deleteCharAt(prikeySb.length() - 1).append(")");
                    if (prikeySb.indexOf("`") > 1) {
                        sb.append(prikeySb).append(",");
                    }
                }
                // 索引
                if (StringUtils.isNotEmpty(indexes)) {
                    StringBuilder indexSb = new StringBuilder();
                    List<MysqlIndexJsonBean> indexLists = JSONArray.parseArray(indexes, MysqlIndexJsonBean.class);
                    if (indexLists != null && indexLists.size() > 0) {
                        for (MysqlIndexJsonBean index : indexLists) {
                            StringBuffer commStr = new StringBuffer();
                            if (StringUtils.isNotBlank(index.getColumns())) {
                                if (index.getColumns().contains("(")) {
                                    String column = index.getColumns().substring(0, index.getColumns().indexOf("("));
                                    commStr.append("`")
                                        .append(index.getName())
                                        .append("`(`")
                                        .append(column)
                                        .append("`(")
                                        .append(index.getColumns().substring(index.getColumns().indexOf("(") + 1,
                                            index.getColumns().lastIndexOf(")")))
                                        .append("))");
                                } else {
                                    commStr.append("`")
                                        .append(index.getName())
                                        .append("`(`")
                                        .append(index.getColumns())
                                        .append("`)");
                                }
                            }
                            if (StringUtils.isNotBlank(index.getType())) {
                                if (index.getType().equals("NORMAL")) {
                                    indexSb.append(" KEY ").append(commStr);
                                } else if (index.getType().equals("UNIQUE")) {
                                    indexSb.append(" UNIQUE KEY ").append(commStr);
                                } else if (index.getType().equals("FULLTEXT")) {
                                    indexSb.append(" FULLTEXT KEY ").append(commStr);
                                }
                            }

                            if (StringUtils.isNotBlank(index.getMethod())) {
                                indexSb.append(" USING ").append(index.getMethod());
                            }
                            if (StringUtils.isNotBlank(index.getColumns())) {
                                indexSb.append(",");
                            }
                        }
                        sb.append(indexSb);
                    }
                }
                checkSbuiler.append(sb).deleteCharAt(sb.lastIndexOf(",")).append(")");
                // 外键
                if (StringUtils.isNotBlank(references)) {
                    StringBuffer refBf = new StringBuffer();
                    List<MysqlRefJsonBean> refLists = JSONArray.parseArray(references, MysqlRefJsonBean.class);
                    if (refLists != null && refLists.size() > 0) {
                        for (MysqlRefJsonBean ref : refLists) {
                            if (StringUtils.isNotBlank(ref.getName())) {
                                refBf.append(" CONSTRAINT `").append(ref.getName()).append("` FOREIGN KEY (");
                            }
                            if (StringUtils.isNotBlank(ref.getColumns())) {
                                StringBuffer includeColumn = new StringBuffer();
                                StringBuffer refColumn = new StringBuffer();
                                String[] includeColArr = ref.getColumns().split(",");
                                for (String i : includeColArr) {
                                    includeColumn.append("`").append(i).append("`,");
                                }
                                if (StringUtils.isNotBlank(ref.getRefDBName())
                                    || StringUtils.isNotBlank(ref.getRefTableName())
                                    || StringUtils.isNotBlank(ref.getRefColumns())) {
                                    refBf.append(includeColumn.deleteCharAt(includeColumn.length() - 1))
                                        .append(") REFERENCES `")
                                        .append(ref.getRefDBName())
                                        .append("`.`")
                                        .append(ref.getRefTableName())
                                        .append("` (");
                                    String[] refColArr = ref.getRefColumns().split(",");
                                    for (String r : refColArr) {
                                        refColumn.append("`").append(r).append("`,");
                                    }
                                    refBf.append(refColumn.deleteCharAt(refColumn.length() - 1))
                                        .append(") ON DELETE ")
                                        .append(ref.getDeleteAction())
                                        .append(" ON UPDATE ")
                                        .append(ref.getUpdateAction());
                                }
                                refBf.append(",");
                            }
                        }
                        sb.append(refBf);
                    }
                }
                sb.deleteCharAt(sb.lastIndexOf(",")).append(")");
                // 公共参数
                if (StringUtils.isNotBlank(engine)) {
                    sb.append(" ENGINE=").append(engine);
                }
                if (StringUtils.isNotBlank(charset)) {
                    sb.append(" DEFAULT CHARACTER SET=").append(charset).append(" COLLATE=").append(collate);
                }
                if (StringUtils.isNotBlank(comment)) {
                    sb.append(" COMMENT='").append(comment).append("'");
                }
                if (StringUtils.isNotBlank(autoIncrement)) {
                    sb.append(" AUTO_INCREMENT=").append(autoIncrement);
                }
                if (StringUtils.isNotBlank(rowFormat)) {
                    sb.append(" ROW_FORMAT=").append(rowFormat);
                }
                if (StringUtils.isNotBlank(avgRowLength)) {
                    sb.append(" AVG_ROW_LENGTH=").append(avgRowLength);
                }
                if (StringUtils.isNotBlank(maxRows)) {
                    sb.append(" MAX_ROWS=").append(maxRows);
                }
                if (StringUtils.isNotBlank(minRows)) {
                    sb.append(" MIN_ROWS=").append(minRows);
                }
                if (StringUtils.isNotBlank(keyBlockSize)) {
                    sb.append(" KEY_BLOCK_SIZE=").append(keyBlockSize);
                }
                showSqlStr.add(sb.toString());
                // 检验sql
                String validateSQLStr = checkSbuiler.toString().replaceAll("`", "");
                Map<Boolean, List<String>> validateSql = SqlValidateHelper.validate(validateSQLStr);
                Iterator<Entry<Boolean, List<String>>> it = validateSql.entrySet().iterator();
                while (it.hasNext()) {
                    Entry<Boolean, List<String>> vali = it.next();
                    if (vali.getKey()) {
                        map.put("root", showSqlStr);
                        map.put("success", true);
                        map.put("failure", false);
                        map.put("dbName", dbName);
                    } else {
                        map.put("root", vali.getValue());
                        map.put("success", false);
                        map.put("failure", true);
                        map.put("dbName", dbName);
                    }
                }
            } catch (Exception e) {
                map.put("root", e.getMessage());
                map.put("success", false);
                map.put("failure", true);
                map.put("dbName", dbName);
            }
        }
        return map;
    }

    private void localExecSqlScript(String objectType, Long databaseId, Long datasourceId, Integer dataSource,
                                    String baseType, String script,
                                    Map<String, Object> map) throws CommonDataBaseException {
        CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
        dataBaseDetail = commonDataSourceService.findDataBase(databaseId,
            datasourceId,
            DataSource.getDataBaseType(dataSource),
            DataBaseType.getDataBaseType(baseType));
        Connection conn = null;
        try {
            conn = dataBaseDetail.getDataSource().getConnection(1000);
            if (objectType.equalsIgnoreCase(WebSqlContants.WEBSQL_PROCEDURE)
                || objectType.equalsIgnoreCase(WebSqlContants.WEBSQL_FUNCTION)) {
                DBUtil.createProcedure(conn, script);
            } else {
                String[] execSql = script.split(";");
                for (String sql : execSql) {
                    if (StringUtils.isNotBlank(sql)) {
                        DBUtil.update(conn, sql);
                    }
                }
            }
            map.put("root", "执行成功");
            map.put("success", true);
            map.put("failure", false);
        } catch (Exception e) {
            LOG.error(e.getMessage());
            map.put("root", "执行失败,异常信息：" + ExceptionUtils.getMessage(e));
            map.put("success", false);
            map.put("failure", true);
        } finally {
            DBUtil.closeDBResources(null, null, conn);
        }
    }

    @RequestMapping("/executeScript")
    @ResponseBody
    public Map<String, Object> executeScript(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            String script = request.getParameter("script");
            String dbName = request.getParameter("dbName");
            Long databaseId = Long.parseLong(request.getParameter("databaseId"));
            Long datasourceId = Long.parseLong(request.getParameter("datasourceId"));
            Integer dataSource = Integer.parseInt(request.getParameter("dataSource"));
            String baseType = request.getParameter("baseType");
            String auserEmail = request.getParameter("auserEmail");
            String isLocal = request.getParameter("isLocal") == null ? "false" : request.getParameter("isLocal");
            String objectType = request.getParameter("objectType") == null ? "other" : request
                .getParameter("objectType");
            if ("isLocal".equals(isLocal)) {
                this.localExecSqlScript(objectType, databaseId, datasourceId, dataSource, baseType, script, map);
                return map;
            }
            if (StringUtils.isBlank(auserEmail)) {
                map.put("root", "审核负责人不能为空");
                map.put("success", false);
                map.put("failure", true);
                return map;
            } else {
                if (!auserEmail.matches(Constants.REG_EMAIL)) {
                    map.put("root", "邮箱格式不正确,必须以@gomeplus.com结尾");
                    map.put("success", false);
                    map.put("failure", true);
                    return map;
                }
            }
            String sqlType = request.getParameter("sqlType");
            String usages = request.getParameter("sqlUsage");
            String remark = request.getParameter("comment") == null ? "" : request.getParameter("comment");
            String project = request.getParameter("projectName");
            if (StringUtils.isBlank(script) || StringUtils.isBlank(dbName)) {
                map.put("root", "参数为空");
                map.put("success", false);
                map.put("failure", true);
            } else {
                SQLUploadBean bean = new SQLUploadBean();
                bean.setAdId(databaseId);
                Long sqlId = new Date().getTime();
                bean.setSqlId(sqlId);
                String commitEmail = this.getCurrentUserObj().getEmail();
                bean.setEmail(commitEmail);
                bean.setAuser(auserEmail);
                bean.setAuserEmail(auserEmail);
                if (StringUtils.isEmpty(this.getDepartMentByAccount(auserEmail.split("@")[0]))) {
                    map.put("root",
                        "提交数据失败，您当前输入的部门负责人账户(" + auserEmail + ")在OA系统中不存在,请确定部门负责人的账户地址为正确邮件地址! \n如不理解,请联系管理员");
                    map.put("failure", true);
                    map.put("success", false);
                    return map;
                }
                bean.setEdept(this.getDepartMentByAccount(auserEmail.split("@")[0]));
                bean.setSqlType(sqlType);
                if (StringUtils.isNotEmpty(dbName)) {
                    bean.setDbName(dbName);
                }
                bean.setUploadType("text");
                bean.setAppVersion("V1.0.0");
                bean.setSqlText(script);
                bean.setSqlBackup("");
                Date date = new Date();
                bean.setCreateTime(date);
                bean.setAstatus("0");
                bean.setUsages(usages);
                bean.setRemark(remark);
                bean.setProject(project);
                boolean flag = uploadService.insert(bean);
                if (flag) {
                    map.put("root", "您好,恭喜已成功完成第一步，请到主菜单【sql审核提交】菜单项中进行审批操作！");
                    map.put("success", true);
                    map.put("failure", false);
                    return map;
                } else {
                    map.put("root", "提交失败");
                    map.put("success", false);
                    map.put("failure", true);
                    return map;
                }
            }
        } catch (Exception e) {
            map.put("root", "异常信息：" + ExceptionUtils.getMessage(e));
            map.put("success", false);
            map.put("failure", true);
        }
        return map;
    }

    @RequestMapping(value = "/table/getDBTableStructure", method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> getDBTableStructure(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        Map<String, Object> resultMap = Maps.newHashMap();
        try {
            String email = super.getCurrentUser();
            String tableName = request.getParameter("tableName");
            String dbName = request.getParameter("dbName");
            String baseType = request.getParameter("baseType");
            Long dataBaseId = Long.parseLong(request.getParameter("dataBaseId"));
            Long dataSourceId = Long.parseLong(request.getParameter("dataSourceId"));
            Integer dataSource = Integer.parseInt(request.getParameter("dataSource"));
            String isLocal = request.getParameter("isLocal");
            CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
            if (isLocal.equals("isLocal")) {
                dataBaseDetail = commonDataSourceService.findDataBase(dataBaseId,
                    dataSourceId,
                    DataSource.getDataBaseType(dataSource),
                    DataBaseType.getDataBaseType(baseType));
            } else {
                dataBaseDetail = commonDataSourceService.getSingleDataBaseByHttp(dataSourceId,
                    DataBaseType.getDataBaseType(baseType.toLowerCase()),
                    email,
                    this.getDBAInterfaceIp(),
                    this.getDiffEnvironmentValue());
            }
            resultMap = webSqlEngine.getTableStructer(dataBaseDetail,
                dbName,
                tableName,
                true,
                true,
                true,
                false,
                false);
        } catch (Exception e) {
            resultMap.put("success", false);
            resultMap.put("failure", true);
            resultMap.put("root", ExceptionUtils.getMessage(e));
        }
        map.put("responseText", resultMap);
        return map;
    }

    @RequestMapping(value = "/table/executeScriptByMainPanel", method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> executeScriptByMainPanel(HttpServletRequest request, ModelAndView mav) {
        Map<String, Object> map = Maps.newHashMap();
        Map<String, Object> resultMap = Maps.newHashMap();
        try {
            String script = request.getParameter("script");
            String dbName = request.getParameter("dbName");
            String tableName = request.getParameter("tableName");
            String databaseType = request.getParameter("databaseType");
            Long dataSourceId = Long.parseLong(request.getParameter("dataSourceId"));
            Integer dataSource = Integer.parseInt(request.getParameter("dataSource"));// 数据源来源
            Long databaseId = Long.parseLong(request.getParameter("databaseId"));
            String isLocal = request.getParameter("isLocal") == null ? "false" : request.getParameter("isLocal");

            if (StringUtils.isBlank(script) || StringUtils.isBlank(dbName) || StringUtils.isBlank(tableName)
                || StringUtils.isBlank(databaseType)) {
                resultMap.put("root", "参数不能为空");
                resultMap.put("success", false);
                resultMap.put("failure", true);
            }
            if (isLocal.equals("isLocal")) {
                this.localExecSqlScript("other", databaseId, dataSourceId, dataSource, databaseType, script, resultMap);
                map.put("responseText", resultMap);
                return map;
            }
            SQLUploadBean bean = new SQLUploadBean();
            bean.setAdId(databaseId);
            Long sqlId = new Date().getTime();
            bean.setSqlId(sqlId);
            String commitEmail = this.getCurrentUserObj().getEmail();
            bean.setEmail(commitEmail);
            if (StringUtils.isNotEmpty(dbName)) {
                bean.setDbName(dbName);
            }
            bean.setUploadType("text");
            bean.setAppVersion("V1.0.0");
            bean.setSqlText(script);
            Date date = new Date();
            bean.setCreateTime(date);
            bean.setAstatus("0");
            boolean flag = uploadService.insert(bean);
            if (flag) {
                resultMap.put("root", "您好,提交成功,恭喜已成功完成第一步，请到主菜单【sql审核提交】菜单项中进行数据补全及审核操作！");
                resultMap.put("success", true);
                resultMap.put("failure", false);
            } else {
                resultMap.put("root", "提交失败");
                resultMap.put("success", false);
                resultMap.put("failure", true);
            }
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
            resultMap.put("root", ExceptionUtils.getMessage(e));
            resultMap.put("success", false);
            resultMap.put("failure", true);
        }
        map.put("responseText", resultMap);
        return map;
    }

    @RequestMapping(value = "/exportBaseView", method = RequestMethod.GET)
    public ModelAndView exportBaseView(HttpServletRequest request, ModelAndView mav, Long dataBaseId, Long dataSourceId,
                                       String baseType, Integer dataSource, String dbName, String objectName,
                                       String operation, String params) {
        String email = super.getCurrentUser();
        String isLocal = request.getParameter("isLocal") == null ? "false" : request.getParameter("isLocal");
        try {
            CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
            if ("isLocal".equals(isLocal)) {
                mav.addObject("isLocal", isLocal);
                dataBaseDetail = commonDataSourceService.findDataBase(dataBaseId,
                    dataSourceId,
                    DataSource.getDataBaseType(dataSource),
                    DataBaseType.getDataBaseType(baseType));
            } else {
                dataBaseDetail = commonDataSourceService.getSingleDataBaseByHttp(dataSourceId,
                    DataBaseType.getDataBaseType(baseType.toLowerCase()),
                    email,
                    this.getDBAInterfaceIp(),
                    this.getDiffEnvironmentValue());
            }
            // 所属项目组
            List<String> userContainsProject = this.getCurrentUserProjects();
            List<Map<String, Object>> proLists = Lists.newArrayList();
            if (userContainsProject != null && userContainsProject.size() > 0) {
                for (String uPro : userContainsProject) {
                    Map<String, Object> map = Maps.newHashMap();
                    map.put("data", uPro);
                    map.put("label", uPro);
                    proLists.add(map);
                }
            } else {
                Map<String, Object> map = Maps.newHashMap();
                map.put("data", "");
                map.put("label", "");
                proLists.add(map);
            }
            // 所有库
            List<DataBaseVO> allDBInfo = webSqlEngine.getDataBase(dataBaseDetail);
            //JSONObject jsonObj = JSON.parseObject(params);
            //String exportType = jsonObj.getString("exportType");
            mav.addObject("isLocal", isLocal);
            mav.addObject("dataBaseId", dataBaseId);
            mav.addObject("dataSourceId", dataSourceId);
            mav.addObject("baseType", baseType);
            mav.addObject("dataSource", dataSource);
            //mav.addObject("objectType", exportType);
            mav.addObject("tabId", objectName);
            mav.addObject("dbName", dbName);
            mav.addObject("requestParams", params);
            mav.addObject("allDBInfo", JSON.toJSONString(allDBInfo));
            mav.addObject("proLists", JSON.toJSONString(proLists));
            mav.setViewName("gmwebsql/export/export_table");
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
        }
        return mav;
    }

    @RequestMapping("/showExportDataList")
    @ResponseBody
    public Map<String, Object> showExportDataList(HttpServletRequest request, String databaseType) {
        Map<String, Object> map = Maps.newHashMap();
        Integer pageNum = Integer.parseInt(request.getParameter("page"));
        Integer pageSize = this.getPageSize();
        List<ExportDataSQL> dataSqlLists = Lists.newArrayList();
        Page<ExportBean> pageLists = null;
        try {
            Map<String, Object> paramsMap = Maps.newHashMap();
            paramsMap.put("databaseType", databaseType);
            paramsMap.put("status", Integer.parseInt(WebSqlContants.STATUS_8));
            if (isAdmin()) {
                pageLists = iExportService.getBeanListsByMap(paramsMap, pageNum, pageSize);
            } else {
                List<String> projects = this.getCurrentUserProjects();
                pageLists = iExportService.getBeanListsByProject(projects, pageNum, pageSize);
            }
            if (pageLists != null && pageLists.size() > 0) {
                for (ExportBean ep : pageLists) {
                    ExportDataSQL jsonEP = JSON.parseObject(ep.getParamJson(), ExportDataSQL.class);
                    dataSqlLists.add(jsonEP);
                }
            }

            map.put("root", dataSqlLists);
            map.put("success", true);
            map.put("failure", false);
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
            map.put("success", false);
            map.put("failure", true);
            map.put("root", "异常信息:" + ExceptionUtils.getMessage(e));
        }
        return map;
    }

    @RequestMapping(value = "/saveExportDataJob", method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> saveExportDataJob(ModelMap modelMap, HttpServletRequest request,
            HttpServletResponse response) {
        Map<String, Object> map = Maps.newHashMap();
        ExportDataSQL paramJsonBean = new ExportDataSQL();
        StringBuilder exportStrB = new StringBuilder();
        ExportBean export = new ExportBean();
        Integer realExportNum = 0; // 实际导出行数
        try {
            // 构建数据源参数
            String email = this.getCurrentUser();
            String isLocal = request.getParameter("isLocal") == null ? "false" : request.getParameter("isLocal");
            String databaseType = request.getParameter("dataBaseType");
            String dataBaseId = request.getParameter("dataBaseId");
            String dataSourceId = request.getParameter("dataSourceId");
            String dataSource = request.getParameter("dataSource");
            CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
            if ("isLocal".equals(isLocal)) {
                dataBaseDetail = commonDataSourceService.findDataBase(Long.parseLong(dataBaseId),
                    Long.parseLong(dataSourceId),
                    DataSource.getDataBaseType(Integer.parseInt(dataSource)),
                    DataBaseType.getDataBaseType(databaseType));
            } else {
                dataBaseDetail = commonDataSourceService.getSingleDataBaseByHttp(Long.parseLong(dataSourceId),
                    DataBaseType.getDataBaseType(databaseType),
                    email,
                    this.getDBAInterfaceIp(),
                    this.getDiffEnvironmentValue());
            }
            // 导出参数
            String fileType = request.getParameter("fileType"); // SQL_Insert
            String fileCharset = request.getParameter("fileCharset");
            String createDesc = request.getParameter("createDesc");
            String exportMethod = request.getParameter("exportMethod");
            String exportMode = request.getParameter("exportMode");
            String dbName = request.getParameter("dbName");
            String tables = StringUtils.isNotBlank(request.getParameter("tables")) ? request
                .getParameter("tables") : "results";
            String sqlTemplate = request.getParameter("sql");
            String tableRowsLimit = request.getParameter("tableRowsLimit");
            String whereCondition = request.getParameter("whereCondition");
            String exportBinary = request.getParameter("exportBinary");
            String exportClob = request.getParameter("exportClob");
            String exportRef = request.getParameter("exportRef");
            String exportIndex = request.getParameter("exportIndex");
            String exportDropTable = request.getParameter("exportDropTable");
            String exportTruncateTable = request.getParameter("exportTruncateTable");
            String projectName = request.getParameter("projectName");
            if (StringUtils.isBlank(projectName)) {
                map.put("success", false);
                map.put("failure", true);
                map.put("root", "项目组名称不能为空");
                return map;
            }
            // 构建导出总行数
            Integer count;
            if (tableRowsLimit.equals("0")) {
                String countSql = "SELECT COUNT(*) FROM " + dbName + "." + tables;
                Map<String, Object> countMap = DBUtil.querySQLCounts(dataBaseDetail.getDataSource().getConnection(1000),
                    countSql,
                    dataBaseDetail.getDataBaseType());
                count = Integer.parseInt(countMap.get("counts").toString());
            } else {
                count = Integer.parseInt(tableRowsLimit);
            }
            // 构建sql语句
            String sql = "";
            if (exportMode.equals("sql")) {
                // 构建数据查询的SQL语句.
                sql = sqlTemplate;
            } else if (exportMode.equals("db")) {
                // 构建db模式下的查表sql语句
                StringBuilder sqlAppend = new StringBuilder();
                sqlAppend.append("SELECT * FROM ").append(dbName).append(".").append(tables).append(" ");
                // 构建where条件
                JSONObject whereJson = JSON.parseObject(whereCondition);
                if (!whereJson.isEmpty()) {
                    String key = "table_" + tables;
                    String where = whereJson.get(key).toString();
                    sqlAppend.append(where).append(" ");
                }
                sql = sqlAppend.toString();
            }

            // 构建导出模式
            boolean exportRefBol = false;
            boolean exportIndexBol = false;
            boolean exportDropBol = false;
            boolean exportTruncate = false;
            boolean exportClobBol = false;
            if (StringUtils.isNotBlank(exportRef) && exportRef.equals(WebSqlContants.EXPORT_YES)) {
                exportRefBol = true;
            }
            if (StringUtils.isNotBlank(exportIndex) && exportIndex.equals(WebSqlContants.EXPORT_YES)) {
                exportIndexBol = true;
            }
            if (StringUtils.isNotBlank(exportDropTable) && exportDropTable.equals(WebSqlContants.EXPORT_YES)) {
                exportDropBol = true;
            }
            if (StringUtils.isNotBlank(exportTruncateTable) && exportTruncateTable.equals(WebSqlContants.EXPORT_YES)) {
                exportTruncate = true;
            }
            if (StringUtils.isNotBlank(exportClob) && exportClob.equals(WebSqlContants.EXPORT_YES)) {
                exportClobBol = true;
            }
            StringBuffer insertBuff = new StringBuffer();
            StringBuffer structBuff = new StringBuffer();
            String createTime = TimeUtils.timestampToToday();
            // 数据与结构
            Object result = webSqlEngine.select(dataBaseDetail, dbName, sql, 1, count);
            Map<String, Object> resultMap = webSqlEngine.getTableStructer(dataBaseDetail,
                dbName,
                tables,
                true,
                exportIndexBol,
                exportRefBol,
                exportDropBol,
                exportTruncate);
            if (result instanceof String) {
                map.put("failure", true);
                map.put("success", false);
                map.put("root", "导出失败,数据结构获取错误");
                return map;
            } else {
                insertBuff = this.getMetaDataBuffer(result, tables, insertBuff);
                SqlResultVO resultVo = (SqlResultVO) result;
                List<Map<String, Object>> dataListMap = resultVo.getData();
                realExportNum = dataListMap == null ? 0 : dataListMap.size();
            }
            // 结构
            Object structObj = resultMap.get("root");
            if (structObj instanceof DBEditTableBean) {
                structBuff = this.getStructBuffer(structObj, structBuff);
            }
            long timestamp = new Date().getTime();// 时间戳
            String fileName = tables + "_" + String.valueOf(timestamp);
            String name = "";
            String path = "";
            if(fileType.equals("EXCEL")){
            	FileOutputStream fos = null;
            	try {
            		SqlResultVO sqlResultVO = (SqlResultVO) result;
            		List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
            		for(DisplayColumnsVO displayColumnsVO : sqlResultVO.getDisplayColumns()){
            			if(StringUtils.isEmpty(displayColumnsVO.getText())){
            				continue;
            			}
            			entity.add(new ExcelExportEntity(displayColumnsVO.getText(), displayColumnsVO.getText(),30));
            		}
            		List<Map<String, Object>> dataMaps = sqlResultVO.getData();
            		int columnIndex = 0;
            		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            		for(int i= 0; i < dataMaps.size() ; i++){
            			Map<String, Object> valueMaps = dataMaps.get(i);
            			Map<String, Object> entitys = new HashMap<String, Object>();
            			for(int j= 0;j<valueMaps.size();j++){
            				ExcelExportEntity e = entity.get(columnIndex);
                			entitys.put(e.getName(), valueMaps.get("COLUMN_" + (columnIndex + 1)));
                			columnIndex ++;
                			if(columnIndex == entity.size()){
                				columnIndex = 0;
                			}
            			}
            			list.add(entitys);
            		}
                    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(tables, tables), entity,
                        list);
                    name = dbName + "_" + tables + "_" + String.valueOf(timestamp) + ".xls";
                    fos = new FileOutputStream(WebSqlContants.EXPORT_LOCAL_ADDS + name);
                    path = WebSqlContants.EXPORT_LOCAL_ADDS + name;
                    workbook.write(fos);
                }finally{
                	if(fos != null){
                		fos.close();
                	}
                }
            }else{
            	// 得出文件所需内容
                if (realExportNum > 0) {
                    exportStrB.append(structBuff).append("\r\n").append(insertBuff);
                } else {
                    exportStrB.append(structBuff).append("\r\n");
                }
                // 压缩文件保存到项目路径下
                byte[] fileStr = exportStrB.toString().getBytes("UTF-8");
                name = dbName + "_" + tables + "_" + String.valueOf(timestamp) + ".zip";
                /* 本地路径：E:\\ 服务器： /app/download_file/ */
                File file = new File(WebSqlContants.EXPORT_LOCAL_ADDS + name);
                path = file.getAbsolutePath();
                // 压缩
                CompressFileUtils.compressByteFile(file, fileStr, "UTF-8", fileName, ".sql");
            }
            String endTime = TimeUtils.timestampToToday();
            // 保存结构化数据信息
            paramJsonBean.setPreRows(realExportNum);
            paramJsonBean.setNowRows(realExportNum);
            paramJsonBean.setExportTabNum(1);
            paramJsonBean.setFileCharset(fileCharset);
            paramJsonBean.setDbName(dbName);
            List<Map<String, Object>> LM = Lists.newArrayList();
            Map<String, Object> subMap = Maps.newHashMap();
            subMap.put("disableForeignKey", true);
            LM.add(subMap);
            paramJsonBean.setExportExtendOption(LM);
            paramJsonBean.setExportClob(StringUtils.isBlank(exportClob) ? "N" : "Y");
            paramJsonBean.setUserName(email);
            paramJsonBean.setExportTabSize(1);// 文件大小
            paramJsonBean.setTimeDelayName("1&#31186;");
            paramJsonBean.setCreateDesc(createDesc);
            paramJsonBean.setExportBinary(StringUtils.isBlank(exportBinary) ? "N" : "Y");
            paramJsonBean.setExportRef(StringUtils.isBlank(exportRef) ? "N" : "Y");
            paramJsonBean.setExportBinary(StringUtils.isBlank(exportBinary) ? "N" : "Y");
            paramJsonBean.setTableRowsLimit(Integer.parseInt(tableRowsLimit));
            paramJsonBean.setGmtCreate(createTime);
            paramJsonBean.setGmtModified(endTime);
            paramJsonBean.setDbUser(this.getCurrentUserObj().getRole());
            paramJsonBean.setExportRef(exportRef);
            paramJsonBean.setExportMethod(exportMethod);
            paramJsonBean.setExportMode(exportMode);
            paramJsonBean.setExecuteTime(TimeUtils.timestampToToday());
            paramJsonBean.setUseExportBlob(exportClobBol);
            String host = SystemInfoUtil.getLocalAddress();
            paramJsonBean.setRemoteHost(host);
            paramJsonBean.setId(iExportService.getMaxId());// 文件id
            paramJsonBean.setExportDropTable(exportDropTable);
            paramJsonBean.setFileType(fileType);
            paramJsonBean.setExportTruncateTable(exportTruncateTable);
            paramJsonBean.setInstanceDesc(host.concat(":").concat(SystemInfoUtil.getServerPort()));
            paramJsonBean.setUseExportDropTable(exportDropBol);
            paramJsonBean.setExportStatus("END");
            paramJsonBean.setStatusName("执行完成");
            paramJsonBean.setExportIndex(exportIndex);
            paramJsonBean.setUseExportIndex(exportIndexBol);
            paramJsonBean.setAllFileName(name);// 文件全名

            export.setFileDetail(path);
            export.setFileName(name);
            export.setParamJson(JSON.toJSONString(paramJsonBean));
            export.setDatabaseType(databaseType);
            export.setProjectName(projectName);
            export.setStatus(Integer.parseInt(WebSqlContants.STATUS_8));
            export.setCreateTime(new Date());
            export.setDownloadUser(email);
            Map<String, Object> localIp = this.getLocalHostIpAndNameAndSysInfo();
            export.setIp(localIp.get("hostIp").toString());
            iExportService.insert(export);

            map.put("success", true);
            map.put("failure", false);
            map.put("root", paramJsonBean.getId());
        } catch (Exception e) {
            LOG.error("异常信息", e);
            map.put("success", false);
            map.put("failure", true);
            map.put("root", "异常信息:" + ExceptionUtils.getMessage(e));
        }
        return map;
    }

    @RequestMapping("/downLoadAllFile")
    @ResponseBody
    public void downLoadAllFile(HttpServletResponse response, Long id) {
        Map<String, Object> map = Maps.newHashMap();
        map.put("id", id);
        map.put("status", Integer.parseInt(WebSqlContants.STATUS_8));
        ExportBean bean = iExportService.getBeanByMap(map);
        response.setCharacterEncoding("UTF-8");
        response.setContentType("multipart/form-data");
        response.setHeader("Content-Disposition", "attachment;fileName=" + bean.getFileName());
        ServletOutputStream out;
        File file = new File(bean.getFileDetail());
        try {
            FileInputStream fileInput = new FileInputStream(file);
            out = response.getOutputStream();
            int b = 0;
            byte[] buffer = new byte[1024000];
            while ((b = fileInput.read(buffer)) != -1) {
                out.write(buffer, 0, b);
            }
            fileInput.close();
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
            LOG.error(ExceptionUtils.getMessage(e));
        }
    }

    @RequestMapping(value = "/deleteJob", method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> deleteJob(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        Long id = Long.parseLong(request.getParameter("id"));
        ExportBean deleBean = new ExportBean();
        deleBean.setId(id);
        deleBean.setStatus(Integer.parseInt(WebSqlContants.STATUS_0));
        boolean flag = iExportService.update(deleBean);
        if (flag) {
            map.put("root", "删除成功");
            map.put("success", true);
            map.put("failure", false);
        } else {
            map.put("root", "删除失败");
            map.put("success", false);
            map.put("failure", true);
        }
        return map;
    }

    @RequestMapping(value = "/view/createViewBase", method = RequestMethod.GET)
    public ModelAndView createViewBase(HttpServletRequest request, ModelAndView mav, Long dataBaseId, Long dataSourceId,
                                       String baseType, Integer dataSource, String dbName, String objectName,
                                       String operation, String params) {
        String isLocal = request.getParameter("isLocal") == null ? "false" : request.getParameter("isLocal");
        try {
            List<String> userContainsProject = this.getCurrentUserProjects();
            List<Map<String, Object>> proLists = Lists.newArrayList();
            if (userContainsProject != null && userContainsProject.size() > 0) {
                for (String uPro : userContainsProject) {
                    Map<String, Object> map = Maps.newHashMap();
                    map.put("data", uPro);
                    map.put("label", uPro);
                    proLists.add(map);
                }
            } else {
                Map<String, Object> map = Maps.newHashMap();
                map.put("data", "");
                map.put("label", "");
                proLists.add(map);
            }
            String tableName = "";
            if (operation.equals("change")) tableName = objectName;
            mav.addObject("proLists", JSON.toJSONString(proLists));
            mav.addObject("tableName", tableName);
            mav.addObject("isLocal", isLocal);
            mav.addObject("dataBaseId", dataBaseId);
            mav.addObject("dataSourceId", dataSourceId);
            mav.addObject("baseType", baseType);
            mav.addObject("dataSource", dataSource);
            mav.addObject("tabId", objectName);
            mav.addObject("nowDBName", dbName);
            mav.addObject("operation", operation);
            mav.setViewName("gmwebsql/view/view_base");
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
        }
        return mav;
    }

    @RequestMapping(value = "/view/showViewScript", method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> showViewScript(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        StringBuilder viewAppend = new StringBuilder();
        try {
            String baseType = request.getParameter("baseType");
            if (baseType.equals("drds")) {
                map.put("root", "异常信息:DRDS数据源不支持创建视图");
                map.put("success", false);
                map.put("failure", true);
                return map;
            }
            String viewName = request.getParameter("name");
            String algorithm = request.getParameter("algorithm");
            // String definer = request.getParameter("definer");// 定义者暂时不需要
            String sqlSecurity = request.getParameter("sqlSecurity");
            String checkOption = request.getParameter("checkOption");
            String content = request.getParameter("content");
            String dbName = request.getParameter("dbName");
            String oldViewName = request.getParameter("oldViewName");
            if (oldViewName == null && StringUtils.isBlank(oldViewName)) {
                viewAppend.append("CREATE ");
            } else {
                viewAppend.append("CREATE OR REPLACE ");
            }
            if (DataBaseType.Oracle.getTypeFullName() == baseType) {
                viewAppend.append("VIEW ")
                    .append(dbName)
                    .append(".")
                    .append(viewName)
                    .append(" \r\n AS ")
                    .append(content);
            } else {
                if (StringUtils.isBlank(algorithm)) {
                    algorithm = "UNDEFINED";
                }
                if (StringUtils.isBlank(sqlSecurity)) {
                    sqlSecurity = "DEFINER";
                }
                if (StringUtils.isBlank(checkOption)) {
                    checkOption = "LOCAL";
                }
                viewAppend.append("\r\n ALGORITHM=")
                    .append(algorithm)
                    .append(" \r\n")
                    .append(" SQL SECURITY ")
                    .append(sqlSecurity)
                    .append("\r\n VIEW ")
                    .append(dbName)
                    .append(".")
                    .append(viewName)
                    .append(" AS \r\n (")
                    .append(content)
                    .append(")\r\n WITH ")
                    .append(checkOption)
                    .append(" CHECK OPTION");
            }
            map.put("root", viewAppend.toString());
            map.put("success", true);
            map.put("failure", false);
        } catch (Exception e) {
            map.put("root", "异常信息:" + ExceptionUtils.getMessage(e));
            map.put("success", false);
            map.put("failure", true);
        }
        return map;
    }

    @RequestMapping(value = "/view/showViews", method = RequestMethod.GET)
    @ResponseBody
    public Map<String, Object> showViews(HttpServletRequest request, Long dataBaseId, String baseType,
                                         Long dataSourceId, Integer dataSource, String isLocal) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            if (baseType.equals("drds")) {
                map.put("root", "异常信息:DRDS数据源不支持创建视图");
                map.put("success", false);
                map.put("failure", true);
                return map;
            }
            if (isLocal == null || StringUtils.isBlank(isLocal)) isLocal = "false";
            String dbName = request.getParameter("dbName");
            String email = this.getCurrentUser();
            CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
            if ("isLocal".equals(isLocal)) {
                dataBaseDetail = commonDataSourceService.findDataBase(dataBaseId,
                    dataSourceId,
                    DataSource.getDataBaseType(dataSource),
                    DataBaseType.getDataBaseType(baseType));
            } else {
                dataBaseDetail = commonDataSourceService.getSingleDataBaseByHttp(dataSourceId,
                    DataBaseType.getDataBaseType(baseType.toLowerCase()),
                    email,
                    this.getDBAInterfaceIp(),
                    this.getDiffEnvironmentValue());
            }
            List<TableVO> viewTableName = webSqlEngine.getTable(dataBaseDetail, dbName, new String[] { "VIEW" });
            map.put("root", viewTableName);
            map.put("success", true);
            map.put("failure", false);
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
            map.put("root", "异常信息:" + ExceptionUtils.getMessage(e));
            map.put("success", false);
            map.put("failure", true);
        }

        return map;
    }

    @RequestMapping(value = "/view/findViewDescriptorByName", method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> findViewDescriptorByName(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            String email = this.getCurrentUser();
            String dbName = request.getParameter("dbName");
            String viewName = request.getParameter("viewName");
            String baseType = request.getParameter("baseType");
            if (baseType.equals("drds")) {
                map.put("root", "异常信息:DRDS数据源不支持创建视图");
                map.put("success", false);
                map.put("failure", true);
                return map;
            }
            Long dataBaseId = Long.parseLong(request.getParameter("dataBaseId"));
            Long dataSourceId = Long.parseLong(request.getParameter("dataSourceId"));
            Integer dataSource = Integer.parseInt(request.getParameter("dataSource"));
            String isLocal = request.getParameter("isLocal") == null ? "false" : request.getParameter("isLocal");
            CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
            if ("isLocal".equals(isLocal)) {
                dataBaseDetail = commonDataSourceService.findDataBase(dataBaseId,
                    dataSourceId,
                    DataSource.getDataBaseType(dataSource),
                    DataBaseType.getDataBaseType(baseType));
            } else {
                dataBaseDetail = commonDataSourceService.getSingleDataBaseByHttp(dataSourceId,
                    DataBaseType.getDataBaseType(baseType.toLowerCase()),
                    email,
                    this.getDBAInterfaceIp(),
                    this.getDiffEnvironmentValue());
            }
            map = webSqlEngine.getViewContent(dataBaseDetail, dbName, viewName);
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
            map.put("root", "异常信息:" + ExceptionUtils.getMessage(e));
            map.put("success", false);
            map.put("failure", true);
        }
        return map;
    }

    @RequestMapping("/showAllCharsetAndCollations")
    @ResponseBody
    public Map<String, Object> showAllCharsetAndCollations(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            String dbType = request.getParameter("dbType");
            List<DBAllCharsetAndCollation> dataTree = iDataCharsetService.getAllCharsetAndCollationsByDBType(dbType,
                WebSqlContants.CHECKOUT_RULE);
            map.put("root", dataTree);
            map.put("success", true);
            map.put("failure", false);
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
            map.put("root", "异常信息:" + ExceptionUtils.getMessage(e));
            map.put("success", false);
            map.put("failure", true);
        }
        return map;
    }

    @RequestMapping("/showColumnsChangeScript")
    @ResponseBody
    public Map<String, Object> showColumnsChangeScript(HttpServletRequest request, MysqlColumnJsonBean values) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            StringBuilder columnBuilder = new StringBuilder();
            StringBuffer fieldSb = new StringBuffer();
            String dbName = request.getParameter("dbName");
            String tableName = request.getParameter("tableName");
            String dbType = request.getParameter("baseType");
            columnBuilder.append("ALTER TABLE ").append(" ").append(dbName).append(".").append(tableName);
            String oldColumnName = request.getParameter("oldColumnName");
            if (oldColumnName == null || StringUtils.isBlank(oldColumnName)) {
                columnBuilder.append(" ADD ").append("\r\n");
            } else {
                columnBuilder.append(" MODIFY ").append("\r\n");
            }
            if (dbType.equals("oracle")) {
                if (StringUtils.isNotBlank(values.getColumnName())) {
                    columnBuilder.append(values.getColumnName()).append(" ");
                }
                if (StringUtils.isNotBlank(values.getColumnType())) {
                    columnBuilder.append(values.getColumnType()).append("(");
                    if (StringUtils.isNotBlank(values.getLength())) {
                        columnBuilder.append(values.getLength()).append(")").append(" ");
                    }
                }
                if (StringUtils.isNotBlank(values.getDefaults())) {
                    columnBuilder.append(" DEFAULT ").append(values.getDefaults());
                }
                if (values.getNullable() != null && !values.getNullable()) {
                    columnBuilder.append(" NOT NULL ");
                }
                // 字段注释
                if (StringUtils.isNotBlank(values.getComments())) {
                    fieldSb.append("COMMENT ON COLUMN ")
                        .append(dbName)
                        .append(".")
                        .append(tableName)
                        .append(".")
                        .append(values.getColumnName())
                        .append(" IS '")
                        .append(values.getComments())
                        .append("';");
                }
                // 主键
                StringBuffer priBuf = new StringBuffer();
                priBuf.append(" PRIMARY KEY (");
                if (values.getPrimaryKey() != null && values.getPrimaryKey()) {
                    priBuf.append("`").append(values.getColumnName()).append("`").append(",");
                }
                priBuf.deleteCharAt(priBuf.length() - 1).append(")");
                if (priBuf.indexOf("`") > 1) {
                    columnBuilder.append(priBuf.toString().replaceAll("`", ""));
                }
            } else {
                if (StringUtils.isNotEmpty(values.getColumnName())) {
                    columnBuilder.append(" COLUMN ").append("`").append(values.getColumnName()).append("`").append(" ");
                }
                if (StringUtils.isNotEmpty(values.getColumnType())) {
                    columnBuilder.append(values.getColumnType()).append("(");
                    if (StringUtils.isNotEmpty(values.getLength())) {
                        columnBuilder.append(values.getLength()).append(")").append(" ");
                    }
                }
                if (values.getNullable() != null && !values.getNullable()) {
                    columnBuilder.append(" NOT NULL ");
                } else {
                    columnBuilder.append(" NULL ");
                }
                if (values.getAutoIncrement() != null && values.getAutoIncrement()) {
                    columnBuilder.append(" AUTO_INCREMENT ");
                }
                if (StringUtils.isNotEmpty(values.getDefaults())) {
                    if (values.getColumnType().contains("int") || values.getColumnType().contains("float")
                        || values.getColumnType().contains("double") || values.getColumnType().contains("decimal")) {
                        columnBuilder.append(" DEFAULT ").append(values.getDefaults());
                    } else {
                        columnBuilder.append(" DEFAULT '").append(values.getDefaults()).append("'");
                    }
                }
                if (values.getUnsigned() != null && values.getUnsigned()) {
                    columnBuilder.append(" UNSIGNED ");
                }
                if (values.getBinary() != null && values.getBinary()) {
                    columnBuilder.append(" BINARY ");
                }
                if (values.getZerofill() != null && values.getZerofill()) {
                    columnBuilder.append(" ZEROFILL ");
                }
                if (StringUtils.isNotEmpty(values.getComments())) {
                    columnBuilder.append(" COMMENT '").append(values.getComments()).append("' ");
                }
                StringBuilder prikeySb = new StringBuilder();
                prikeySb.append("PRIMARY KEY (");
                if (values.getPrimaryKey() != null && values.getPrimaryKey()) {
                    prikeySb.append("`").append(values.getColumnName()).append("`").append(",");
                }
                prikeySb.deleteCharAt(prikeySb.length() - 1).append(")");
                if (prikeySb.indexOf("`") > 1) {
                    columnBuilder.append(prikeySb);
                }
            }
            columnBuilder.append(";");
            if (fieldSb != null && StringUtils.isNotBlank(fieldSb)) {
                columnBuilder.append("\r\n").append(fieldSb);
            }
            map.put("root", columnBuilder.toString());
            map.put("success", true);
            map.put("failure", false);
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
            map.put("root", "异常信息:" + ExceptionUtils.getMessage(e));
            map.put("success", false);
            map.put("failure", true);
        }
        return map;
    }

    @RequestMapping("/showOneColumnDetailInfo")
    @ResponseBody
    public Map<String, Object> showOneColumnDetailInfo(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            String email = this.getCurrentUser();
            String dbName = request.getParameter("dbName");
            String tableName = request.getParameter("tableName");
            String columnName = request.getParameter("columnName");
            String baseType = request.getParameter("baseType");
            if (baseType.equals("drds")) {
                map.put("root", "异常信息:DRDS数据源不支持此操作");
                map.put("success", false);
                map.put("failure", true);
                return map;
            }
            Long dataBaseId = Long.parseLong(request.getParameter("dataBaseId"));
            Long dataSourceId = Long.parseLong(request.getParameter("dataSourceId"));
            Integer dataSource = Integer.parseInt(request.getParameter("dataSource"));
            String isLocal = request.getParameter("isLocal") == null ? "false" : request.getParameter("isLocal");
            CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
            if ("isLocal".equals(isLocal)) {
                dataBaseDetail = commonDataSourceService.findDataBase(dataBaseId,
                    dataSourceId,
                    DataSource.getDataBaseType(dataSource),
                    DataBaseType.getDataBaseType(baseType));
            } else {
                dataBaseDetail = commonDataSourceService.getSingleDataBaseByHttp(dataSourceId,
                    DataBaseType.getDataBaseType(baseType.toLowerCase()),
                    email,
                    this.getDBAInterfaceIp(),
                    this.getDiffEnvironmentValue());
            }
            map = webSqlEngine.getOneColumnDetailInfo(dataBaseDetail, dbName, tableName, columnName);
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
            map.put("root", "异常信息:" + ExceptionUtils.getMessage(e));
            map.put("success", false);
            map.put("failure", true);
        }
        return map;
    }

    @RequestMapping("/showIndexesChangeScript")
    @ResponseBody
    public Map<String, Object> showIndexesChangeScript(HttpServletRequest request, MysqlIndexJsonBean indexParams) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            StringBuilder indexBuilder = new StringBuilder();
            StringBuffer mysqlIndexBuff = new StringBuffer();
            StringBuffer oracleIndexBuff = new StringBuffer();
            String dbName = request.getParameter("dbName");
            String tableName = request.getParameter("tableName");
            String dbType = request.getParameter("baseType");
            mysqlIndexBuff.append("ALTER TABLE ").append(" ").append(dbName).append(".").append(tableName);
            if (indexParams.getOldIndexName() == null || StringUtils.isBlank(indexParams.getOldIndexName())) {
                mysqlIndexBuff.append(" ADD ").append("\r\n");
            } else {
                mysqlIndexBuff.append(" DROP INDEX ")
                    .append(indexParams.getOldIndexName())
                    .append(";")
                    .append("\r\n")
                    .append("ALTER TABLE ")
                    .append(" ")
                    .append(dbName)
                    .append(".")
                    .append(tableName)
                    .append(" ADD ")
                    .append("\r\n");
                oracleIndexBuff.append("DROP INDEX ")
                    .append(dbName)
                    .append(".")
                    .append(indexParams.getOldIndexName())
                    .append(";")
                    .append("\r\n");
            }
            if (dbType.equals("oracle")) {
                StringBuffer commStr = new StringBuffer();
                if (StringUtils.isNotBlank(indexParams.getColumns())) {
                    String column = "";
                    if (indexParams.getColumns().contains("(")) {
                        column = indexParams.getColumns().substring(0, indexParams.getColumns().indexOf("("));
                    } else {
                        column = indexParams.getColumns();
                    }
                    commStr.append(indexParams.getName())
                        .append(" ON ")
                        .append(dbName)
                        .append(".")
                        .append(tableName)
                        .append("(")
                        .append(column)
                        .append(" ")
                        .append(indexParams.getMethod())
                        .append(")");
                }
                if (StringUtils.isNotBlank(indexParams.getType())) {
                    if (indexParams.getType().equals("INDEX")) {
                        oracleIndexBuff.append("CREATE INDEX ").append(dbName).append(".").append(commStr);
                    } else if (indexParams.getType().equals("UNIQUE")) {
                        oracleIndexBuff.append("CREATE UNIQUE INDEX ").append(dbName).append(".").append(commStr);
                    } else if (indexParams.getType().equals("BITMAP")) {
                        oracleIndexBuff.append("CREATE BITMAP INDEX ").append(dbName).append(".").append(commStr);
                    }
                }
                if (StringUtils.isNotBlank(indexParams.getColumns())) {
                    oracleIndexBuff.append(";");
                }
                indexBuilder.append(oracleIndexBuff);
            } else {
                StringBuffer commStr = new StringBuffer();
                if (StringUtils.isNotBlank(indexParams.getColumns())) {
                    if (indexParams.getColumns().contains("(")) {
                        String column = indexParams.getColumns().substring(0, indexParams.getColumns().indexOf("("));
                        commStr.append("`")
                            .append(indexParams.getName())
                            .append("`(`")
                            .append(column)
                            .append("`(")
                            .append(indexParams.getColumns().substring(indexParams.getColumns().indexOf("(") + 1,
                                indexParams.getColumns().lastIndexOf(")")))
                            .append("))");
                    } else {
                        commStr.append("`")
                            .append(indexParams.getName())
                            .append("`(`")
                            .append(indexParams.getColumns())
                            .append("`)");
                    }
                }
                if (StringUtils.isNotBlank(indexParams.getType())) {
                    if (indexParams.getType().equals("NORMAL")) {
                        mysqlIndexBuff.append(" KEY ").append(commStr);
                    } else if (indexParams.getType().equals("UNIQUE")) {
                        mysqlIndexBuff.append(" UNIQUE KEY ").append(commStr);
                    } else if (indexParams.getType().equals("FULLTEXT")) {
                        mysqlIndexBuff.append(" FULLTEXT KEY ").append(commStr);
                    } else if (indexParams.getType().equals("PRIMARY")) {
                        mysqlIndexBuff.append(" PRIMARY KEY ").append(commStr);
                    }
                }

                if (StringUtils.isNotBlank(indexParams.getMethod())) {
                    mysqlIndexBuff.append(" USING ").append(indexParams.getMethod());
                }
                if (StringUtils.isNotBlank(indexParams.getColumns())) {
                    mysqlIndexBuff.append(";");
                }
                indexBuilder.append(mysqlIndexBuff);
            }
            map.put("root", indexBuilder.toString());
            map.put("success", true);
            map.put("failure", false);
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
            map.put("root", "异常信息:" + ExceptionUtils.getMessage(e));
            map.put("success", false);
            map.put("failure", true);
        }
        return map;
    }

    @RequestMapping("/showOneIndexDetailInfo")
    @ResponseBody
    public Map<String, Object> showOneIndexDetailInfo(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            String email = this.getCurrentUser();
            String dbName = request.getParameter("dbName");
            String tableName = request.getParameter("tableName");
            String indexName = request.getParameter("indexName");
            String baseType = request.getParameter("baseType");
            if (baseType.equals("drds")) {
                map.put("root", "异常信息:DRDS数据源不支持此操作");
                map.put("success", false);
                map.put("failure", true);
                return map;
            }
            Long dataBaseId = Long.parseLong(request.getParameter("dataBaseId"));
            Long dataSourceId = Long.parseLong(request.getParameter("dataSourceId"));
            Integer dataSource = Integer.parseInt(request.getParameter("dataSource"));
            String isLocal = request.getParameter("isLocal") == null ? "false" : request.getParameter("isLocal");
            CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
            if ("isLocal".equals(isLocal)) {
                dataBaseDetail = commonDataSourceService.findDataBase(dataBaseId,
                    dataSourceId,
                    DataSource.getDataBaseType(dataSource),
                    DataBaseType.getDataBaseType(baseType));
            } else {
                dataBaseDetail = commonDataSourceService.getSingleDataBaseByHttp(dataSourceId,
                    DataBaseType.getDataBaseType(baseType.toLowerCase()),
                    email,
                    this.getDBAInterfaceIp(),
                    this.getDiffEnvironmentValue());
            }
            map = webSqlEngine.getOneIndexDetailInfo(dataBaseDetail, dbName, tableName, indexName);
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
            map.put("root", "异常信息:" + ExceptionUtils.getMessage(e));
            map.put("success", false);
            map.put("failure", true);
        }
        return map;
    }

    // Start 可编程对象
    @RequestMapping("/showProgramObjects")
    @ResponseBody
    public SqlTemplateVo showProgramObjects(HttpServletRequest request, String dbType, Long dataBaseId,
                                            Long dataSourceId, Integer dataSource, String isLocal) {
        SqlTemplateVo templateVo = new SqlTemplateVo();
        String dbName = request.getParameter("dbName");
        try {
            isLocal = StringUtils.isBlank(isLocal) ? "false" : isLocal;
            String email = this.getCurrentUser();
            CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
            if ("isLocal".equals(isLocal)) {
                dataBaseDetail = commonDataSourceService.findDataBase(dataBaseId,
                    dataSourceId,
                    DataSource.getDataBaseType(dataSource),
                    DataBaseType.getDataBaseType(dbType));
            } else {
                dataBaseDetail = commonDataSourceService.getSingleDataBaseByHttp(dataSourceId,
                    DataBaseType.getDataBaseType(dbType.toLowerCase()),
                    email,
                    this.getDBAInterfaceIp(),
                    this.getDiffEnvironmentValue());
            }
            templateVo = webSqlEngine.getAllProcedureFunTrigEventInfo(dataBaseDetail, dbName, true, true, true, true);
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
        }
        return templateVo;
    }

    @RequestMapping("/getProcedurePage")
    public ModelAndView getProcedurePage(HttpServletRequest request, ModelAndView mav, Long dataBaseId,
                                         Long dataSourceId, Integer dataSource, String dbName, String baseType,
                                         String funcprocType, String objectName, String objectType, String operation,
                                         String tabId, String params) {
        String isLocal = request.getParameter("isLocal") == null ? "false" : request.getParameter("isLocal");
        List<String> userContainsProject = this.getCurrentUserProjects();
        List<Map<String, Object>> proLists = Lists.newArrayList();
        if (userContainsProject != null && userContainsProject.size() > 0) {
            for (String uPro : userContainsProject) {
                Map<String, Object> map = Maps.newHashMap();
                map.put("data", uPro);
                map.put("label", uPro);
                proLists.add(map);
            }
        } else {
            Map<String, Object> map = Maps.newHashMap();
            map.put("data", "");
            map.put("label", "");
            proLists.add(map);
        }
        String email = this.getCurrentUser();
        String oldProceName = "", oldProceContent = "";
        if (operation.equals("edit")) {
            try {
                CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
                if ("isLocal".equals(isLocal)) {
                    dataBaseDetail = commonDataSourceService.findDataBase(dataBaseId,
                        dataSourceId,
                        DataSource.getDataBaseType(dataSource),
                        DataBaseType.getDataBaseType(baseType));
                } else {
                    dataBaseDetail = commonDataSourceService.getSingleDataBaseByHttp(dataSourceId,
                        DataBaseType.getDataBaseType(baseType.toLowerCase()),
                        email,
                        this.getDBAInterfaceIp(),
                        this.getDiffEnvironmentValue());
                }
                Map<String, Object> map = webSqlEngine.getProcedureFuncTrigEventDetailInfo(dataBaseDetail,
                    dbName,
                    objectName,
                    WebSqlContants.WEBSQL_PROCEDURE);
                oldProceName = objectName;
                if (map != null && map.size() > 0) {
                    oldProceContent = JSON.toJSONString(map.get("content").toString())
                        .substring(1, JSON.toJSONString(map.get("content").toString()).length() - 1)
                        .replaceAll("'", "\"");
                } else {
                    oldProceContent = "";
                }
            } catch (Exception e) {
                LOG.error(ExceptionUtils.getMessage(e));
            }
        }
        mav.addObject("proLists", JSON.toJSONString(proLists));
        mav.addObject("dataBaseId", dataBaseId);
        mav.addObject("dataSourceId", dataSourceId);
        mav.addObject("baseType", baseType);
        mav.addObject("dataSource", dataSource);
        mav.addObject("isLocal", isLocal);
        mav.addObject("objectType", objectType);
        mav.addObject("dbName", dbName);
        mav.addObject("tabId", tabId);
        mav.addObject("oldProceName", oldProceName);
        mav.addObject("oldProceContent", oldProceContent);
        mav.addObject("envType", this.getDiffEnvironmentValue());
        mav.setViewName("gmwebsql/procedure/procedure");
        return mav;
    }

    @RequestMapping("/getProcedureConfig")
    @ResponseBody
    public Map<String, Object> getProcedureConfig(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            String script = request.getParameter("script");
            String dbName = request.getParameter("dbName");
            DataProcedureBean procedureBean = new DataProcedureBean();
            procedureBean.setDbName(dbName);
            if (StringUtils.isBlank(script)) {
                map.put("failure", true);
                map.put("success", false);
                map.put("root", "-1");
                return map;
            } else {
                String upScript = script.toUpperCase();
                String preValue = upScript.substring(0, upScript.indexOf("CREATE")).trim();
                if (StringUtils.isNotBlank(preValue)) {
                    map.put("failure", true);
                    map.put("success", false);
                    map.put("root", "提交的脚本请使用创建存储过程的CREATE PROCEDURE的语法[C1]");
                    return map;
                }
                if (!upScript.contains("PROCEDURE")) {
                    map.put("failure", true);
                    map.put("success", false);
                    map.put("root", "提交的脚本并非CREATE PROCEDURE的语法[C2]");
                    return map;
                }
                if (upScript.split("PROCEDURE")[1].contains("(")) {
                    String name = upScript.substring(upScript.indexOf("PROCEDURE"),
                        upScript.split("PROCEDURE")[1].indexOf("("));
                    if (StringUtils.isBlank(name)) {
                        map.put("failure", true);
                        map.put("success", false);
                        map.put("root", "没有找到存储过程的名称及参数的位置");
                        return map;
                    } else {
                        procedureBean.setName(name);
                        String[] params = upScript.substring(upScript.indexOf("(") + 1, upScript.indexOf(")"))
                            .split(",");
                        if (params != null && params.length > 0) {
                            List<ProcedureInnerBean> inLists = Lists.newArrayList();
                            for (String param : params) {
                                ProcedureInnerBean innerBean = new ProcedureInnerBean();
                                innerBean.setName(param);
                                inLists.add(innerBean);
                            }
                            procedureBean.setParams(inLists);
                            ProcedureInnerBean lastBean = new ProcedureInnerBean();
                            lastBean.setName(params[params.length - 1]);
                            procedureBean.setLastParam(lastBean);
                        }
                    }
                } else {
                    map.put("failure", true);
                    map.put("success", false);
                    map.put("root", "没有找到存储过程的名称及参数的位置");
                    return map;
                }
                if (upScript.contains("SQL SECURITY")) {
                    String securityName = upScript.contains("INVOKER") ? "INVOKER" : "DEFINER";
                    procedureBean.setSqlSecurity(securityName);
                    procedureBean.setsQLSecurityDisplay("SQL SECURITY " + securityName);
                }
                if (upScript.contains("CONTAINS SQL")) {
                    procedureBean.setDataCallMethod("CONTAINS SQL");
                } else if (upScript.contains("NO SQL")) {
                    procedureBean.setDataCallMethod("NO SQL");
                } else if (upScript.contains("READS SQL DATA")) {
                    procedureBean.setDataCallMethod("READS SQL DATA");
                } else if (upScript.contains("MODIFIES SQL DATA")) {
                    procedureBean.setDataCallMethod("MODIFIES SQL DATA");
                }
                if (!upScript.contains("NOT DETERMINISTIC")) {
                    map.put("root", "在：[ NOT d ]附近存在语法错误");
                    map.put("success", false);
                    map.put("failure", true);
                    return map;
                }
                if (!upScript.contains("BEGIN") || !upScript.contains("END")) {
                    map.put("root", "存在语法错误,begin or end缺失");
                    map.put("success", false);
                    map.put("failure", true);
                    return map;
                }
                procedureBean
                    .setDeterministic(upScript.contains("NOT DETERMINISTIC") ? "NOT DETERMINISTIC" : "DETERMINISTIC");
            }
            map.put("root", procedureBean);
            map.put("success", true);
            map.put("failure", false);
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
            map.put("root", "异常信息:" + ExceptionUtils.getMessage(e));
            map.put("success", false);
            map.put("failure", true);
        }
        return map;
    }

    @RequestMapping("/setProcedureConfig")
    @ResponseBody
    public Map<String, Object> setProcedureConfig(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        StringBuilder sb = new StringBuilder();

        try {
            String script = request.getParameter("script").toUpperCase();
            String sqlSecurity = request.getParameter("sqlSecurity");
            String deterministic = request.getParameter("deterministic");
            String dataCallMethod = request.getParameter("dataCallMethod");
            String[] contArray = script.split("BEGIN");
            sb.append(contArray[0]).append("\r\n");
            if (StringUtils.isNotBlank(sqlSecurity)) {
                if (sqlSecurity.contains("INVOKER")) {
                    if (!contArray[0].contains("SQL SECURITY INVOKER")) {
                        sb.append("SQL SECURITY INVOKER").append("\r\n");
                    }
                } else if (sqlSecurity.contains("DEFINER")) {
                    if (!contArray[0].contains("SQL SECURITY DEFINER")) {
                        sb.append("SQL SECURITY DEFINER").append("\r\n");
                    }
                }
            }
            if (StringUtils.isNotBlank(deterministic)) {
                String nd = "";
                if (deterministic.equals("NOT DETERMINISTIC")) {
                    nd = sb.toString().replaceAll("NOT DETERMINISTIC", "NOT DETERMINISTIC");
                } else if (deterministic.equals("DETERMINISTIC")) {
                    nd = sb.toString().replaceAll("DETERMINISTIC", "DETERMINISTIC");
                }
                sb.append(nd).append("\r\n");
            }
            if (StringUtils.isNotBlank(dataCallMethod)) {
                String dmd = "";
                if (dataCallMethod.equals("CONTAINS SQL")) {
                    dmd = sb.toString().replaceAll("CONTAINS SQL", "CONTAINS SQL");
                } else if (dataCallMethod.equals("NO SQL")) {
                    dmd = sb.toString().replaceAll("NO SQL", "NO SQL");
                } else if (dataCallMethod.equals("READS SQL DATA")) {
                    dmd = sb.toString().replaceAll("READS SQL DATA", "READS SQL DATA");
                } else if (dataCallMethod.equals("MODIFIES SQL DATA")) {
                    dmd = sb.toString().replaceAll("MODIFIES SQL DATA", "MODIFIES SQL DATA");
                }
                sb.append(dmd).append("\r\n");
            }
            sb.append("BEGIN").append("\r\n").append(contArray[1]);
            map.put("root", sb.toString());
            map.put("success", true);
            map.put("failure", false);
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
            map.put("root", "异常信息:" + ExceptionUtils.getMessage(e));
            map.put("success", false);
            map.put("failure", true);
        }
        return map;
    }

    @RequestMapping("/function/function-page")
    public ModelAndView getFunctionPage(HttpServletRequest request, ModelAndView mav, Long dataBaseId,
                                        Long dataSourceId, Integer dataSource, String dbName, String baseType,
                                        String funcprocType, String objectName, String objectType, String operation,
                                        String tabId, String params) {
        String isLocal = request.getParameter("isLocal") == null ? "false" : request.getParameter("isLocal");
        List<String> userContainsProject = this.getCurrentUserProjects();
        List<Map<String, Object>> proLists = Lists.newArrayList();
        if (userContainsProject != null && userContainsProject.size() > 0) {
            for (String uPro : userContainsProject) {
                Map<String, Object> map = Maps.newHashMap();
                map.put("data", uPro);
                map.put("label", uPro);
                proLists.add(map);
            }
        } else {
            Map<String, Object> map = Maps.newHashMap();
            map.put("data", "");
            map.put("label", "");
            proLists.add(map);
        }
        String email = this.getCurrentUser();
        String oldFuncName = "", oldFuncContent = "";
        if (operation.equals("edit")) {
            try {
                CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
                if ("isLocal".equals(isLocal)) {
                    dataBaseDetail = commonDataSourceService.findDataBase(dataBaseId,
                        dataSourceId,
                        DataSource.getDataBaseType(dataSource),
                        DataBaseType.getDataBaseType(baseType));
                } else {
                    dataBaseDetail = commonDataSourceService.getSingleDataBaseByHttp(dataSourceId,
                        DataBaseType.getDataBaseType(baseType.toLowerCase()),
                        email,
                        this.getDBAInterfaceIp(),
                        this.getDiffEnvironmentValue());
                }
                Map<String, Object> map = webSqlEngine.getProcedureFuncTrigEventDetailInfo(dataBaseDetail,
                    dbName,
                    objectName,
                    WebSqlContants.WEBSQL_FUNCTION);
                oldFuncName = objectName;
                if (map != null && map.size() > 0) {
                    oldFuncContent = JSON.toJSONString(map.get("content").toString())
                        .substring(1, JSON.toJSONString(map.get("content").toString()).length() - 1)
                        .replaceAll("'", "\"");
                } else {
                    oldFuncContent = "";
                }
            } catch (Exception e) {
                LOG.error(ExceptionUtils.getMessage(e));
            }
        }
        mav.addObject("proLists", JSON.toJSONString(proLists));
        mav.addObject("dataBaseId", dataBaseId);
        mav.addObject("dataSourceId", dataSourceId);
        mav.addObject("baseType", baseType);
        mav.addObject("dataSource", dataSource);
        mav.addObject("isLocal", isLocal);
        mav.addObject("objectType", objectType);
        mav.addObject("dbName", dbName);
        mav.addObject("tabId", tabId);
        mav.addObject("oldFuncName", oldFuncName);
        mav.addObject("oldFuncContent", oldFuncContent);
        mav.addObject("envType", this.getDiffEnvironmentValue());
        mav.setViewName("gmwebsql/function/function");
        return mav;
    }

    @RequestMapping("/trigger/createTrigger")
    public ModelAndView createTrigger(HttpServletRequest request, ModelAndView mav, Long dataBaseId, Long dataSourceId,
                                      Integer dataSource, String dbName, String baseType, String funcprocType,
                                      String objectName, String objectType, String operation, String tabId,
                                      String params) {
        String isLocal = request.getParameter("isLocal") == null ? "false" : request.getParameter("isLocal");
        List<String> userContainsProject = this.getCurrentUserProjects();
        List<Map<String, Object>> proLists = Lists.newArrayList();
        if (userContainsProject != null && userContainsProject.size() > 0) {
            for (String uPro : userContainsProject) {
                Map<String, Object> map = Maps.newHashMap();
                map.put("data", uPro);
                map.put("label", uPro);
                proLists.add(map);
            }
        } else {
            Map<String, Object> map = Maps.newHashMap();
            map.put("data", "");
            map.put("label", "");
            proLists.add(map);
        }
        Map<String, Object> trigItem = Maps.newHashMap();
        String email = this.getCurrentUser();
        try {
            CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
            if ("isLocal".equals(isLocal)) {
                dataBaseDetail = commonDataSourceService.findDataBase(dataBaseId,
                    dataSourceId,
                    DataSource.getDataBaseType(dataSource),
                    DataBaseType.getDataBaseType(baseType));
            } else {
                dataBaseDetail = commonDataSourceService.getSingleDataBaseByHttp(dataSourceId,
                    DataBaseType.getDataBaseType(baseType.toLowerCase()),
                    email,
                    this.getDBAInterfaceIp(),
                    this.getDiffEnvironmentValue());
            }
            List<TableVO> tables = webSqlEngine.getTable(dataBaseDetail, dbName, new String[] { "TABLE" });
            Map<String, Object> trigInner = Maps.newHashMap();
            trigInner.put("allTableNames", tables);
            trigItem.put("helperDo", trigInner);
            trigItem.put("triggerSchema", dbName);
            if (operation.equals("edit")) {
                trigItem.put("triggerName", objectName);
                Map<String, Object> map = webSqlEngine.getProcedureFuncTrigEventDetailInfo(dataBaseDetail,
                    dbName,
                    objectName,
                    WebSqlContants.WEBSQL_TRIGGER);
                if (map != null && map.size() > 0) {
                    trigItem.put("actionStatement", map.get("actionStatement"));
                    trigItem.put("actionTiming", map.get("actionTiming"));
                    trigItem.put("eventManipulation", map.get("eventManipulation"));
                    trigItem.put("eventObjectTable", map.get("eventObjectTable"));
                }
            } else {
                trigItem.put("triggerName", "");
            }
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
        }
        mav.addObject("proLists", JSON.toJSONString(proLists));
        mav.addObject("triggerItem", JSON.toJSONString(trigItem));
        mav.addObject("dataBaseId", dataBaseId);
        mav.addObject("dataSourceId", dataSourceId);
        mav.addObject("baseType", baseType);
        mav.addObject("dataSource", dataSource);
        mav.addObject("isLocal", isLocal);
        mav.addObject("objectType", objectType);
        mav.addObject("dbName", dbName);
        mav.addObject("tabId", tabId);
        mav.addObject("option", operation);
        mav.addObject("envType", this.getDiffEnvironmentValue());
        mav.setViewName("gmwebsql/trigger/trigger");
        return mav;
    }

    @RequestMapping("/trigger/getAddTriggerSaveSql")
    @ResponseBody
    public Map<String, Object> getAddTriggerSql(HttpServletRequest request) {
        Map<String, Object> map = Maps.newHashMap();
        try {
            String triggerSchema = request.getParameter("triggerSchema");
            String triggerName = request.getParameter("triggerName");
            String eventObjectTable = request.getParameter("eventObjectTable");
            String actionTiming = request.getParameter("actionTiming");
            String eventManipulation = request.getParameter("eventManipulation");
            String actionStatement = request.getParameter("actionStatement");
            StringBuilder sb = new StringBuilder();
            sb.append("CREATE TRIGGER ");
            if (StringUtils.isNotBlank(triggerSchema)) {
                sb.append(triggerSchema).append(".").append(triggerName).append("\r\n");
            }
            if (StringUtils.isNotBlank(actionTiming)) {
                sb.append(actionTiming).append(" ");
                if (StringUtils.isNotBlank(eventManipulation)) {
                    sb.append(eventManipulation)
                        .append(" ON ")
                        .append(triggerSchema)
                        .append(".")
                        .append(eventObjectTable)
                        .append("\r\n ")
                        .append("FOR EACH ROW ");
                }
            }
            if (StringUtils.isNotBlank(actionStatement)) {
                sb.append("\r\n").append(actionStatement);
            }
            List<String> list = Lists.newArrayList();
            list.add(sb.toString());
            map.put("root", list);
            map.put("failure", false);
            map.put("success", true);
        } catch (Exception e) {
            LOG.error(ExceptionUtils.getMessage(e));
            map.put("root", "异常信息:" + ExceptionUtils.getMessage(e));
            map.put("success", false);
            map.put("failure", true);
        }
        return map;
    }

    @RequestMapping("/event/createEvent")
    public ModelAndView createEvent(HttpServletRequest request, ModelAndView mav, Long dataBaseId, Long dataSourceId,
                                    Integer dataSource, String dbName, String baseType, String funcprocType,
                                    String objectName, String objectType, String operation, String tabId,
                                    String params) {
        String isLocal = request.getParameter("isLocal") == null ? "false" : request.getParameter("isLocal");
        List<String> userContainsProject = this.getCurrentUserProjects();
        List<Map<String, Object>> proLists = Lists.newArrayList();
        if (userContainsProject != null && userContainsProject.size() > 0) {
            for (String uPro : userContainsProject) {
                Map<String, Object> map = Maps.newHashMap();
                map.put("data", uPro);
                map.put("label", uPro);
                proLists.add(map);
            }
        } else {
            Map<String, Object> map = Maps.newHashMap();
            map.put("data", "");
            map.put("label", "");
            proLists.add(map);
        }
        String email = this.getCurrentUser();
        String oldEventName = "", oldEventContent = "";
        if (operation.equals("edit")) {
            try {
                CommonDataBaseDetail dataBaseDetail = new CommonDataBaseDetail();
                if ("isLocal".equals(isLocal)) {
                    dataBaseDetail = commonDataSourceService.findDataBase(dataBaseId,
                        dataSourceId,
                        DataSource.getDataBaseType(dataSource),
                        DataBaseType.getDataBaseType(baseType));
                } else {
                    dataBaseDetail = commonDataSourceService.getSingleDataBaseByHttp(dataSourceId,
                        DataBaseType.getDataBaseType(baseType.toLowerCase()),
                        email,
                        this.getDBAInterfaceIp(),
                        this.getDiffEnvironmentValue());
                }
                Map<String, Object> map = webSqlEngine.getProcedureFuncTrigEventDetailInfo(dataBaseDetail,
                    dbName,
                    objectName,
                    WebSqlContants.WEBSQL_EVENT);
                oldEventName = objectName;
                if (map != null && map.size() > 0) {
                    oldEventContent = JSON.toJSONString(map.get("content").toString())
                        .substring(1, JSON.toJSONString(map.get("content").toString()).length() - 1)
                        .replaceAll("'", "\"");
                } else {
                    oldEventContent = "";
                }
            } catch (Exception e) {
                LOG.error(ExceptionUtils.getMessage(e));
            }
        }
        mav.addObject("proLists", JSON.toJSONString(proLists));
        mav.addObject("dataBaseId", dataBaseId);
        mav.addObject("dataSourceId", dataSourceId);
        mav.addObject("baseType", baseType);
        mav.addObject("dataSource", dataSource);
        mav.addObject("isLocal", isLocal);
        mav.addObject("objectType", objectType);
        mav.addObject("dbName", dbName);
        mav.addObject("tabId", tabId);
        mav.addObject("oldEventName", oldEventName);
        mav.addObject("oldEventContent", oldEventContent);
        mav.addObject("envType", this.getDiffEnvironmentValue());
        mav.setViewName("gmwebsql/event/event");
        return mav;
    }
    // End 可编程对象
}
